SQL分组查询找组内最大

1、问题

现在有数据表，存储着各个学生的各个科目的成绩。如果查出每门课程得分最高的学生该怎么找呢？
正常的思路可能是先按分组查出每个科目的最高成绩，然后再将这个结果和原表做一个连接查询(按照科目和成绩相等连接)，这样才可以查出想要的结果。或者是，多写几个子查询，应该也能解决问题。
但是，这些答案都不好，没有充分发挥SQL语法中关系代数的优势。本文主要讲如何通过自连接，解决这个问题。

2、准备

2.1 表结构

建立如下表结构：

create table Grade (
    StudentName varchar2(50),
    CourseName varchar2(100),
    Grade number
);

这里省略了主键和索引等。

2.2 数据

执行如下脚本，向表中插入数据：

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('John Smith', 'English', 76);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('John Wall', 'Math', 86);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Kobe Byrant', 'Math', 90);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Will Smith', 'English', 96);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Li Lei', 'Math', 99);


INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Tim Duncan', 'English', 95);

COMMIT;

数据如下：

STUDENTNAME	COURSENAME	GRADE
John Smith	English	76
John Wall	Math	86
Kobe Byrant	Math	90
Will Smith	English	96
Li Lei	Math	99
Tim Duncan	English	95

3、查询语句

3.1 思路

这里的解决方案是做一个自连接(连接条件：t.CourseName = tt.CourseName AND tt.grade >= t.grade)，查找比当前记录分数高的记录只有一条的记录(这一条自然就是记录本身了)。自连接之后的筛选方法是，先按照t.StudentName, t.CourseName分组，然后，筛选出只有一条记录的组就好了。

3.2 自连接SQL v1.0

根据上面的思路，可以写出如下的SQL：

SELECT t.StudentName,
       t.CourseName
FROM Grade t,
     Grade tt
WHERE t.CourseName = tt.CourseName
  AND tt.grade >= t.grade
GROUP BY t.StudentName,
         t.CourseName
HAVING count(1) <= 1

结果如下：

STUDENTNAME	COURSENAME
Will Smith	English
Li Lei	Math

3.3 自连接SQL v2.0

前面的结果中，没法看到各个科目成绩最高的学生的分数，因为分数字段没有出现在group by表达式中。
这个非常好解决，不会有相同StudentName和CourseName，但是成绩不同的情况，所以，直接在group by子句中加上这个字段就行。如下。

SELECT t.CourseName,
       t.StudentName,
       t.grade
FROM Grade t,
     Grade tt
WHERE t.CourseName = tt.CourseName
  AND tt.grade >= t.grade
GROUP BY t.StudentName,
         t.CourseName,
         t.grade
HAVING count(1) <= 1

查询结果如下：

COURSENAME	STUDENTNAME	GRADE
English	Will Smith	96
Math	Li Lei	99

3.4 查询各个科目的成绩排名

根据这个原理，可以很容易地查询各个科目的成绩排名。
自连接之后，有多少条比当前记录大的(实际是大于等于)，就认为排名是第几。

SELECT t.CourseName,
       t.StudentName,
       t.grade,
       count(1) CourseRank
FROM Grade t,
     Grade tt
WHERE t.CourseName = tt.CourseName
  AND tt.grade >= t.grade
GROUP BY t.StudentName,
         t.CourseName,
         t.grade
order by t.CourseName, CourseRank

结果如下：

COURSENAME	STUDENTNAME	GRADE	COURSERANK
English	Will Smith	96	1
English	Tim Duncan	95	2
English	John Smith	76	3
Math	Li Lei	99	1
Math	Kobe Byrant	90	2
Math	John Wall	86	3

3.5 Oracle中`partition by`语句的实现

利用oracle中的partition by语句，可以比较方便的实现按照字段分组，并且在分组内按照指定字段排序的需求，如下是一个例子：

SELECT CourseName,
       StudentName,
       Grade,
       rank() over(PARTITION BY CourseName
                   ORDER BY Grade ASC) CourseRank
FROM grade

查询结果如下：

COURSENAME	STUDENTNAME	GRADE	COURSERANK
English	Will Smith	96	1
English	Tim Duncan	95	2
English	John Smith	76	3
Math	Li Lei	99	1
Math	Kobe Byrant	90	2
Math	John Wall	86	3

这里的rank函数可以换成其他的函数，实现不同的排序需求。比如控制两个相同分数的排名是否相同、以及如何处理并列排名之后的名次问题等。

4、说明

这个问题，oracle中应该有专门的partition by语法专门解决类似问题，MySql中应该也有GROUP_CONCAT聚合函数能够配合SUBSTRING_INDEX截取函数可以实现类似功能。但是，这些高级的语法有一个局限性，他们不通用。了解这种自连接的思路可以帮助我们更好地理解SQL的强大语法特性。
当然，在工作中，可以根据具体的环境，尽量采用专门的语句来实现，这样可读性会好些。

最后编辑于：2021.06.20 00:06:09