MySQL 分组排序

比如有一个全年级成绩表（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