一、数据准备

 

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<=b.score group by b.class HAVING COUNT(*)<=3) order by a.class,a.score desc;

但是当高分出现重复情况，由于高分记录数变多，会失效，比如此例中的classOne

预期结果应该95,95,82，但82并未显示

 换一种思路，增加一个伪列，每个班级按成绩高低排序，伪列按班级分组增加序号，取序号前三的即可

select * from (
select @i:= case when @class=a.class then @i + 1 else 1 end as row_no,@class:=a.class as class,a.stu_name,a.score 
from student a,(select @i:=0,@class:='') as b order by a.class,a.score desc) b where b.row_no<=3

（3）mysql实现分组查询每个班级的前三名(分数并列)
select a.class,a.stu_name,a.score from student a where EXISTS (select count(*) from (select distinct class,score from student) b 
where a.class=b.class and a.score<=b.score group by b.class HAVING COUNT(*)<=3) order by a.class,a.score desc;