分组后取每组前几条记录

窗口函数很强大，row_number(顺序排序),rank(跳跃排序),dense_rank(连续排序)

MYSQL 8.0

CREATE TABLE IF NOT EXISTS  student(
   id varchar(20),-- 编号
   class varchar(20),-- 班级
   stu_name varchar(20),-- 学生姓名
   score int-- 分数
);

插入数据:

insert student values('1','classOne','Jack',82);
insert student values('2','classOne','Jame',95);
insert student values('3','classOne','Toney',82);
insert student values('4','classOne','Nike',40);
insert student values('5','classOne','Ha',20);
insert student values('6','classOne','Tom',95);
insert student values('7','classTwo','Elik',40);
insert student values('8','classTwo','T',3);
insert student values('9','classTwo','Kim',60);
insert student values('10','classTwo','Tim',10);
insert student values('11','classTwo','Li',95);
insert student values('12','classThree','C.Ronaldo',60);
insert student values('13','classThree','Messi',40);
insert student values('14','classThree','Neymar',90);
insert student values('15','classThree','Moyi',20);
insert student values('16','classThree','Sun',70);

分组查询每个班级的最高分

SELECT
	a.* 
FROM
	student a
	INNER JOIN ( SELECT max( score ) AS score, class AS class FROM student GROUP BY class ) b ON a.class = b.class 
	AND a.score = b.score 
SELECT
	* 
FROM
	student a 
WHERE
	( SELECT count( 1 ) FROM student b WHERE a.class = b.class AND a.score < b.score ) = 0
SELECT
	* 
FROM
	( SELECT rank ( ) over ( PARTITION BY class ORDER BY a.score DESC) rownumber, a.* FROM student a ) b 
WHERE
	b.rownumber = 1