mysql - 按重要性排序,然后按部分分组
问题描述
在此表方案中http://sqlfiddle.com/#!9/101a9c/9/0
id section rev importance
2 1 b 6
1 1 a 5
3 2 c 9
5 3 e 6
4 3 d 5
我想order by **importance**
那时group by **section**
所以我尝试使用GROUP BY docs.section
SELECT docs.id, docs.section, docs.rev, (
SELECT SUM(docs_importance.importance)
FROM docs_importance
WHERE docs_importance.doc_id = docs.id
) AS importance
FROM docs
GROUP BY docs.section ---GROUPING
ORDER BY docs.section, importance DESC
但得到了这个结果:
id section rev importance
1 1 a 5
3 2 c 9
4 3 d 5
但我想要的结果应该是
id section rev importance
2 1 b 6
3 2 c 9
5 3 e 6
解决方案
这适用于更高版本的 MySql:
with
grp as (
select d.section, d.id, sum(i.importance) imp
from docs d inner join docs_importance i
on i.doc_id = d.id
group by d.section, d.id
),
cte as (
select grp.id, grp.section, grp.imp
from grp inner join (
select section, max(imp) maximp
from grp
group by section
) t on t.section = grp.section and t.maximp = grp.imp
)
select d.id, d.section, d.rev, cte.imp importance
from docs d inner join cte
on cte.id = d.id and cte.section = d.section
查看演示
结果:
| id | section | rev | importance |
| --- | ------- | --- | ---------- |
| 2 | 1 | b | 6 |
| 3 | 2 | c | 9 |
| 5 | 3 | e | 6 |
对于旧版本:
select d.id, d.section, d.rev, cte.imp importance
from docs d inner join (
select grp.id, grp.section, grp.imp
from (
select d.section, d.id, sum(i.importance) imp
from docs d inner join docs_importance i
on i.doc_id = d.id
group by d.section, d.id
) grp inner join (
select section, max(imp) maximp
from (
select d.section, d.id, sum(i.importance) imp
from docs d inner join docs_importance i
on i.doc_id = d.id
group by d.section, d.id
)grp
group by section
) t on t.section = grp.section and t.maximp = grp.imp
)cte
on cte.id = d.id and cte.section = d.section
查看演示
推荐阅读
- javascript - 使用 JavaScript 获取 Infragistics WebDataGrid 中单击的单元格的值
- google-maps-api-3 - GoogleMaps JS 无法在中国加载地图了
- reactjs - 反应无法读取属性“地图”
- bash - 在用户名中带有 @ 和 : 的 scp
- java - Mapstruct 扩展包含具有相同签名的方法的多个接口
- java - 这个类如何在鼠标按下后自动重绘?
- c# - 如何在 C# 中使用 Window Defender 扫描上传文档的病毒
- maven - Maven - 使用构建配置文件排除特定依赖项
- java - 自动连线不起作用,但手动创建对象有效
- google-bigquery - BigQuery 中的 ISO 年周 (YYYYww) 日期格式