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

需求分析

表关系：

• 学生 Student、成绩 Sc、课程 Course、教师 Teacher
• 目标：按每门课程分组，取出该课程分数最高的前 2 名学生
• 规则：同分并列时保留，超出 2 名也一并展示（通用排名逻辑）

一、MySQL 实现（常用，分两种写法）

方式 1：窗口函数（MySQL 8.0+ 推荐，简洁高效）

使用 RANK()/DENSE_RANK() 排名：

1. RANK()：同分同排名，后续名次跳跃（1,1,3）
2. DENSE_RANK()：同分同排名，后续名次连续（1,1,2）

1.1 RANK 版本（常规取前两名）

sql

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) 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 版本（并列名次连续）

sql

SELECT 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 即为前两名

sql

SELECT 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;

补充说明

1. 字段说明：结果包含课程名、学号、姓名、分数，可根据需求删减字段；
2. 并列分数：如果一门课有 3 个学生都是最高分，RANK() 会全部查出（排名都为 1，满足 rk<=2），符合 “前两名” 业务常规理解；
3. 如需严格只返回 2 条（截断并列），可改用 ROW_NUMBER()，但同分学生会随机取舍，不推荐成绩排名场景使用。