查询每门课程最好的前两名

需求分析表关系学生Student、成绩Sc、课程Course、教师Teacher目标按每门课程分组取出该课程分数最高的前 2 名学生规则同分并列时保留超出 2 名也一并展示通用排名逻辑一、MySQL 实现常用分两种写法方式 1窗口函数MySQL 8.0 推荐简洁高效使用RANK()/DENSE_RANK()排名RANK()同分同排名后续名次跳跃1,1,3DENSE_RANK()同分同排名后续名次连续1,1,21.1 RANK 版本常规取前两名sqlSELECT cname, s_no, sname, score FROM ( SELECT c.cname, sc.s_no, s.sname, sc.score, RANK() OVER (PARTITION BY sc.c_no ORDER BY sc.score DESC) AS rk FROM Sc sc JOIN Course c ON sc.c_no c.c_no JOIN Student s ON sc.s_no s.s_no ) t WHERE t.rk 2;1.2 DENSE_RANK 版本并列名次连续sqlSELECT cname, s_no, sname, score FROM ( SELECT c.cname, sc.s_no, s.sname, sc.score, DENSE_RANK() OVER (PARTITION BY sc.c_no ORDER BY sc.score DESC) AS rk FROM Sc sc JOIN Course c ON sc.c_no c.c_no JOIN Student s ON sc.s_no s.s_no ) t WHERE t.rk 2;方式 2子查询自连接兼容 MySQL 5.x无窗口函数思路统计当前课程中分数比自己高的人数人数 2 即为前两名sqlSELECT DISTINCT c.cname, s.s_no, s.sname, sc.score FROM Sc sc1 JOIN Sc sc2 ON sc1.c_no sc2.c_no AND sc1.score sc2.score JOIN Course c ON sc1.c_no c.c_no JOIN Student s ON sc1.s_no s.s_no GROUP BY sc1.c_no, sc1.s_no, sc1.score, c.cname, s.sname HAVING COUNT(*) 2 UNION ALL -- 补充每门课第一名没有比它分数高的记录 SELECT c.cname, s.s_no, s.sname, sc1.score FROM Sc sc1 JOIN Course c ON sc1.c_no c.c_no JOIN Student s ON sc1.s_no s.s_no WHERE NOT EXISTS ( SELECT 1 FROM Sc sc2 WHERE sc2.c_no sc1.c_no AND sc2.score sc1.score ) ORDER BY cname, score DESC;二、SQL Server / Oracle 写法同窗口函数逻辑和 MySQL8.0 语法基本一致Oracle 可省略别名小细节sql-- Oracle / SQL Server SELECT cname, s_no, sname, score FROM ( SELECT c.cname, sc.s_no, s.sname, sc.score, RANK() OVER (PARTITION BY sc.c_no ORDER BY sc.score DESC) rk FROM Sc sc INNER JOIN Course c ON sc.c_no c.c_no INNER JOIN Student s ON sc.s_no s.s_no ) t WHERE rk 2;补充说明字段说明结果包含课程名、学号、姓名、分数可根据需求删减字段并列分数如果一门课有 3 个学生都是最高分RANK()会全部查出排名都为 1满足rk2符合 “前两名” 业务常规理解如需严格只返回 2 条截断并列可改用ROW_NUMBER()但同分学生会随机取舍不推荐成绩排名场景使用。