Mysql 查询每个班级的成绩最好前三名(并列与不并列的情况)

一、数据准备

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);

二、步入正题

（1）mysql实现分组查询每个班级的最高分

思路：使用max函数找出最高的分数，然后去关联 select a.class,a.stu_name,a.score 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 order by a.class;

（2）mysql实现分组查询每个班级的前三名(分数并列只取三个)

网上大多数版本如下：

select a.id,a.class,a.stu_name,a.score from student a where EXISTS (select count(*) from student b where a.class=b.class and a.score