MySQL每组求最值的记录与每组前N条记录

一、表结构与记录

create table students(
	id int auto_increment primary key,
	`name` varchar(200),
    grade int,
    course varchar(200)
);

insert into students(name, grade, course) values("张三", 60, "数学");
insert into students(name, grade, course) values("李四", 70, "数学");
insert into students(name, grade, course) values("王五", 80, "数学");
insert into students(name, grade, course) values("赵六", 90, "数学");
insert into students(name, grade, course) values("孙七", 85, "数学");
insert into students(name, grade, course) values("周八", 75, "数学");
insert into students(name, grade, course) values("吴九", 65, "数学");
insert into students(name, grade, course) values("郑十", 55, "数学");
insert into students(name, grade, course) values("张三", 78, "英语");
insert into students(name, grade, course) values("李四", 89, "英语");
insert into students(name, grade, course) values("王五", 52, "英语");
insert into students(name, grade, course) values("赵六", 86, "英语");
insert into students(name, grade, course) values("孙七", 35, "英语");
insert into students(name, grade, course) values("周八", 85, "英语");
insert into students(name, grade, course) values("吴九", 85, "英语");
insert into students(name, grade, course) values("郑十", 61, "英语");
insert into students(name, grade, course) values("王五", 62, "语文");
insert into students(name, grade, course) values("赵六", 83, "语文");
insert into students(name, grade, course) values("孙七", 77, "语文");
insert into students(name, grade, course) values("周八", 100, "语文");
insert into students(name, grade, course) values("吴九", 92, "语文");
insert into students(name, grade, course) values("郑十", 100, "语文");

二、查询每个学生成绩最高的记录，包括 id、姓名、课程名、成绩

1. 网上一般直接用max或min函数来求

select `name`, course, max(grade) as max_grade from students group by `name`;

name	course	max_grade
吴九	数学	92
周八	数学	100
孙七	数学	85
张三	数学	78
李四	数学	89
王五	数学	80
赵六	数学	90
郑十	数学	100

这样有一个问题，就是group by默认是取每个分组的第一条记录，所以max_grade虽然计算的是该组中最大的成绩，但是course并不是这个成绩对应的实际的课程名，这样就得到了不一致的数据。比如说吴九最高分92是语文，但是查询出来的结果是数学。如果要查询是字段是分组的字段以及最值的字段，那么这个方法是可行的，但是一旦要查询其他的信息，这个方法就错了。

2. 正确语句

select s1.`name`, s1.course, s1.grade from 
   students s1, (select `name`, max(grade) as max_grade from students group by `name`) as s2
      where s1.name=s2.name and s1.grade=s2.max_grade;

name	course	grade
王五	数学	80
赵六	数学	90
孙七	数学	85
张三	英语	78
李四	英语	89
周八	语文	100
吴九	语文	92
郑十	语文	100

先用子查询查询出每组的最值以及分组的字段信息，然后再用where条件进行筛选，这样就可以查询出正确的结果了。

三、查询分组里面的前N条记录----查询每门科目中，成绩为前两名的学生姓名与成绩

1. 成绩相同并且排名在前可以并列

select s1.course, s1.name, s1.grade from students s1
      where (select count(distinct s2.grade) from students s2 
                where s1.course=s2.course and s2.grade>s1.grade) < 2 
                /*当整张表中相同课程里成绩高于s1的人数小于2时，证明s1是前两名，加distinct是为了防止前面有并列的人*/
      order by s1.course;

cours	name	grade
数学	赵六	90
数学	孙七	85
英语	李四	89
英语	赵六	86
语文	周八	100
语文	吴九	92
语文	郑十	100

在语文中，一共查询出来了三个人，是因为周八跟郑十是并列第一，所以第一名有两个人，第二名就是吴九了，也就是成绩可以并列。如果在子查询中没有加distinct，那么查询出来的结果就没有吴九，因为语文成绩比它高的已经有两个人了，分别是第一第二名。

2. 成绩相同的不能并列，只取其中一个

select s1.course, s1.name, s1.grade from (select * from students group by grade,course) as s1
   where (select count(distinct s2.grade) from students s2 
            where s1.course=s2.course and s2.grade>s1.grade) < 2
   order by s1.course;

course	name	grade
数学	孙七	85
数学	赵六	90
英语	李四	89
英语	赵六	86
语文	周八	100
语文	吴九	92

与上面的区别在于对s1进行了筛选，也就是对查询出的记录按照(grade,course)进行了分组，相当于grade与course相同的记录只查询出来一条。

3. 同组去重，grade与course相同的只取一条记录

• 方法一
  select distinct grade, course from students;
• 方法二
  select * from students group by grade,course order by course;