如何用SQL查询各科目成绩排名前三的学生？

建表与测试数据示例 #

Student表 #

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

INSERT INTO Student VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eve'),
(6, 'Frank');

Subject表（含成绩） #

CREATE TABLE Subject (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(50) NOT NULL,
    student_id INT,
    score INT,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

INSERT INTO Subject VALUES
(1, 'Maths', 1, 95),
(2, 'Maths', 2, 92),
(3, 'Maths', 3, 95),
(4, 'Maths', 4, 88),
(5, 'English', 1, 89),
(6, 'English', 2, 94),
(7, 'English', 5, 94),
(8, 'English', 6, 90);

核心需求说明 #

直接用ORDER BY score DESC LIMIT 3只能获取全局成绩最高的3个学生，无法按科目分组筛选。需要按科目分组后对每组内成绩排名，再提取排名≤3的记录。

方案1：窗口函数实现（适配MySQL 8.0+、PostgreSQL、SQL Server 2008+） #

窗口函数是最简洁的实现方式，支持三种排名逻辑，可按需选择：

• ROW_NUMBER()：同分也分配不同排名（如95分排1、2）
• RANK()：同分共享排名，后续排名跳过（如95分排1、1，下一个排3）
• DENSE_RANK()：同分共享排名，后续排名不跳过（如95分排1、1，下一个排2）

示例使用RANK()保留同分并列情况：

SELECT s.student_name, sub.subject_name, sub.score, sub.rank
FROM (
    SELECT 
        subject_name, 
        student_id, 
        score,
        RANK() OVER (PARTITION BY subject_name ORDER BY score DESC) AS rank
    FROM Subject
) AS sub
JOIN Student s ON sub.student_id = s.student_id
WHERE sub.rank <= 3
ORDER BY sub.subject_name, sub.rank;

若需要严格取3个（忽略同分超出的情况），替换为ROW_NUMBER()即可。

方案2：MySQL 5.x兼容方案（无窗口函数） #

针对不支持窗口函数的老版本MySQL，通过子查询统计当前成绩在对应科目中的排名：

SELECT 
    s.student_name, 
    sub1.subject_name, 
    sub1.score
FROM Subject sub1
JOIN Student s ON sub1.student_id = s.student_id
WHERE (
    SELECT COUNT(DISTINCT score)
    FROM Subject sub2
    WHERE sub2.subject_name = sub1.subject_name 
      AND sub2.score > sub1.score
) < 3
ORDER BY sub1.subject_name, sub1.score DESC;

注：COUNT(DISTINCT score)用于处理同分情况，若不需要保留同分，去掉DISTINCT即可。

方案3：SQL Server专属方案（TOP WITH TIES） #

SQL Server支持TOP WITH TIES结合分区逻辑，直接返回每个科目成绩最高的N条（含同分）：

SELECT TOP 3 WITH TIES
    s.student_name,
    sub.subject_name,
    sub.score
FROM Subject sub
JOIN Student s ON sub.student_id = s.student_id
ORDER BY ROW_NUMBER() OVER (PARTITION BY sub.subject_name ORDER BY sub.score DESC);

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