SQL分组查询找组内最大

原文链接 ：https://www.jianshu.com/p/69699e412d37
（保存内容以免原文丢失）

表结构

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

插入数据

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;

思路

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

自连接SQL v1.0

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

自连接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

查询各个科目的成绩排名

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

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

说明

MySql中应该也有GROUP_CONCAT聚合函数能够配合SUBSTRING_INDEX截取函数可以实现类似功能