MySQL每组求最值的记录与每组前N条记录-阿里云开发者社区

开发者社区> 数据库> 正文

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

简介: 一、表结构与记录 create table students( id int auto_increment primary key, `name` varchar(200), grade int, course varchar(200) ); insert int...

一、表结构与记录

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;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章