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

需求：查询每门课程分数排名前两名的学生，表名Sc（字段：c_no课程号、s_no学号、score分数）

一、整体语句拆分

sql

-- 外层查询：取出最终结果 select a.c_no,a.s_no,a.score from Sc a -- 核心：关联子查询做排名筛选 where (select count(distinct score) from Sc b where b.c_no=a.c_no and b.score>=a.score) <= 2 -- 最后按课程、分数倒序排列 order by a.c_no,a.score desc ;

二、核心逻辑（相关子查询 + 分数去重排名）

1. 别名说明

• a：外层表，遍历每一条成绩记录
• b：内层表，和a是同一张成绩表，用来做对比统计

2. 子查询拆解（最关键部分）

sql

select count(distinct score) from Sc b where b.c_no=a.c_no and b.score>=a.score

1. b.c_no = a.c_no只对比同一门课程的成绩，不同课程互不干扰。

2. b.score >= a.score统计：当前这门课中，分数 ≥ 当前学生分数 的所有不同分数有多少个。

3. distinct score对分数去重，代表按「分数档位」排名，不是按人数排名。

4. count(...)统计出「大于等于当前分数的不同分数个数」，这个数字就是当前分数的名次。

3.where 个数 <= 2筛选条件

只保留名次为第 1、第 2 名的记录，也就是每门课前两名。

三、举个例子直观理解

假设课程001成绩数据：

表格

1. 遍历a= (001,01,95)子查询：统计课程 001 中score>=95的不同分数→ 只有95count = 1→1<=2，保留这条数据。

2. 遍历a= (001,02,95)同理count=1→ 保留。

3. 遍历a= (001,03,90)子查询：score>=90的分数：95、90 →count=2→2<=2，保留。

4. 遍历a= (001,04,85)分数：95、90、85 →count=3→ 不保留。

最终结果：95 分两人 + 90 分一人，全部查出（同分并列靠前）。

四、语句特点 & 优缺点

优点

1. 纯标准 SQL，兼容所有数据库（MySQL、Oracle、SQL Server 都能用）
2. 实现同分并列排名（经典DENSE_RANK效果）

缺点

1. 性能差：属于相关子查询，外层每一行都会执行一次子查询，数据量大时很慢。
2. 逻辑只适用于「按分数档位排名」，和 “按人数取前 2” 有区别。

五、补充：两种排名场景区别（易踩坑）

1. 本语句效果：按分数排名（并列不占位）多人同分第一，依然都算在前两名内。
2. 如果需求是「最多取 2 条记录（按人数取前 2）」这条 SQL 不适用，需要改用窗口函数（row_number()）。

六、现代写法（MySQL8.0+/Oracle 推荐，性能更好）

用窗口函数替代子查询，逻辑更清晰、效率更高：

sql

-- 每门课按分数倒序排名，取前2名（并列排名） SELECT c_no, s_no, score FROM ( SELECT c_no, s_no, score, DENSE_RANK() OVER(PARTITION BY c_no ORDER BY score DESC) rk FROM Sc ) t WHERE rk <= 2;