MySQL 分组排序

零下两度

已于 2024-06-27 10:46:12 修改

阅读量681

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 数据库

于 2019-08-27 15:52:00 首次发布

本文链接：https://blog.csdn.net/bt_xxx/article/details/100099837

mysql 专栏收录该内容

7 篇文章

订阅专栏

这篇博客探讨了在MySQL中如何查询分组后的单条和多条记录。对于查询每个班级的最高分，文章指出直接使用MAX函数可能会导致数据不匹配。在MySQL 5.7及更高版本中，可以通过子查询配合LIMIT解决。同时，如果需要查询每个班级的前两名成绩，可以使用子查询或窗口函数来实现。

比如有一个全年级成绩表（grade）：

id	name（姓名）	score（分数）	class_id（班级）
1	张三	68	1
2	李四	70	1
3	王五	85	1
4	刘六	63	2
5	陈七	90	2
......	......	......	......

一，查询分组后单条记录

比如现要求查询出每个班级成绩最好的：

1，分组后取最大的（错误）

SELECT *, MAX(score) as maxscore FROM `grade` GROUP BY class_id

这个sql只能查询出每个班级的最高分（class_id，maxscore）,其它字段数据完全不是最高分的匹配记录

2，排序后分组（看版本）

--mysq <= 5.6 
SELECT * FROM (SELECT * FROM `grade` ORDER BY score DESC) as f GROUP BY class_id

--mysq > 5.6 
 SELECT * FROM (SELECT * FROM `grade` ORDER BY score DESC LIMIT 10000000 ) as f GROUP BY class_id

5.7之后（包括5.7）对子查询排序做了优化，子查询全表排序失效，但可以在子查询里面加limit，排序能继续生效

3，子查询

SELECT a.* FROM `grade` a WHERE score = (SELECT MAX(score) FROM `grade` WHERE class_id = a.class_id)

注意：如果一个班级有多个相同的最高分score的话，查出结果单个班级会出多条记录，只要一条的话可以在sql后面加GROUP BY class_id

二，查询分组后多条记录

比如要求查出每个班级成绩最好的两条数据：

1，子查询

SELECT a.* FROM `grade` a WHERE ( 
    SELECT COUNT(*) FROM `grade` WHERE class_id = a.class_id AND a.score < score
) < 2

2，窗口函数

--mysql >= 8.0
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY score DESC) as rowno FROM `grade`
) f WHERE rowno <=2