SQL如何查询每个教授分组下所有成绩为最小值的学生记录

• 需要筛选出每位教授名下成绩为最低值的所有学生记录
• 同分规则：如果同一位教授名下有多名学生成绩并列最低，所有符合条件的学生都要纳入结果。例如Augustinus教授有两名学生成绩均为1.0，这两名学生都需要保留在结果中
• 核心目标：查询每位教授对应最优成绩的全部学生记录
• 备注：本次查询使用的数据表名为temp

当前尝试编写的SQL如下：

SELECT professor, student, min(note)
FROM temp
GROUP BY professor
ORDER BY note

这个写法的问题是：按professor字段分组后，仅能返回每位教授对应的1条最低成绩记录，无法返回所有成绩等于该组最低值的学生记录，不满足同分并列全部返回的要求。

方法1：窗口函数写法（推荐，逻辑清晰性能好） #

使用RANK()窗口函数，按教授分区、成绩升序排名，筛选每个分区排名为1的记录即可。RANK()会自动为同分记录分配相同排名，刚好满足并列最低分全部返回的需求：

SELECT professor, student, note
FROM (
    SELECT
        *,
        RANK() OVER (PARTITION BY professor ORDER BY note ASC) AS score_rank
    FROM temp
) ranked_res
WHERE score_rank = 1
ORDER BY note, professor, student

方法2：关联子查询写法（兼容不支持窗口函数的旧SQL版本） #

先聚合查询出每位教授对应的最低成绩，再通过关联原表，匹配出所有「教授+最低成绩」组合对应的学生记录：

SELECT t1.professor, t1.student, t1.note
FROM temp t1
JOIN (
    SELECT professor, MIN(note) AS lowest_note
    FROM temp
    GROUP BY professor
) t2
ON t1.professor = t2.professor AND t1.note = t2.lowest_note
ORDER BY t1.note, t1.professor, t1.student

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