SQL查询每位教授对应所有最优成绩学生的实现方法

你原先的SQL写法逻辑不成立：按Prof字段分组后，结果集的粒度是「每个教授一行」，此时直接查询Student字段不符合SQL语法规范——因为单个教授分组下对应多个学生，数据库无法确定要返回哪个学生的值。只有旧版本MySQL关闭ONLY_FULL_GROUP_BY校验时，这条语句才会勉强执行，执行时会随机抽取每个教授分组下的任意一条学生记录返回，必然会遗漏同教授下成绩并列最优的其他学生。

根据你使用的数据库版本，选以下任意一种写法即可，两种写法都会完整返回每位教授名下所有成绩为最优值的学生记录，不会遗漏同分数据。

方案1：窗口函数写法（推荐，支持MySQL8.0+、PostgreSQL、SQL Server等主流新版数据库） #

用RANK()窗口函数按教授分区、按成绩升序打排名，相同成绩会拿到相同排名，最后筛选排名为1的记录即可：

WITH student_rank AS (
    SELECT
        Prof,
        Student,
        Grade,
        RANK() OVER (PARTITION BY Prof ORDER BY Grade ASC) AS score_rank
    FROM x
)
SELECT Prof, Student, Grade
FROM student_rank
WHERE score_rank = 1;

注意不要用ROW_NUMBER()替换RANK()：ROW_NUMBER()会给同分记录分配不同的序号，最终还是只会返回每位教授的1条学生记录，不符合需求。

方案2：关联子查询写法（兼容不支持窗口函数的旧版数据库，如MySQL5.x） #

先通过子查询计算出每位教授对应的最优（最低）成绩，再关联原表筛选出所有成绩匹配对应教授最优值的记录：

SELECT t1.Prof, t1.Student, t1.Grade
FROM x t1
WHERE t1.Grade = (
    SELECT MIN(t2.Grade)
    FROM x t2
    WHERE t2.Prof = t1.Prof
);

以上两种写法跑你给出的样例数据，都会返回教授A下学生1、学生2（成绩1.0），教授B下学生3（成绩1.2）的全部记录，和预期结果一致。

内容的提问来源于stack exchange，提问作者Total Recall