MySQL：如何用子查询按班级和课程对学生成绩排名

现有表结构及数据 #

表名：Scores

查询需求 #

• 查询字段：student_id、class_id、course、score 以及 rank（排名字段）
• 排名规则：按class_id和course分组排名，同班级同课程内按分数降序、student_id降序排序（分数相同时，student_id更大的排名靠前）
• 限制条件：仅允许使用子查询，不能使用rank()等窗口/分区函数
• 结果排序：按class_id降序、course降序、score降序、student_id降序排列

预期结果 #

当前困境 #

能实现全局排名，但无法按相同class_id和course分组进行排名。

解决方案 #

核心思路是通过子查询统计同一班级、同一课程中，分数高于当前记录，或分数相同但student_id更大的记录数量，该数量加1即为当前记录的排名。

对应的SQL语句：

SELECT 
    s1.student_id,
    s1.class_id,
    s1.course,
    s1.score,
    (
        SELECT COUNT(*) + 1
        FROM Scores s2
        WHERE s2.class_id = s1.class_id
          AND s2.course = s1.course
          AND (s2.score > s1.score OR (s2.score = s1.score AND s2.student_id > s1.student_id))
    ) AS rank
FROM Scores s1
ORDER BY s1.class_id DESC, s1.course DESC, s1.score DESC, s1.student_id DESC;

逻辑说明 #

1. 外层查询遍历Scores表的每一条记录（记为s1）
2. 内层子查询针对每条s1记录，统计满足以下条件的s2记录数：
   • 与s1同班级、同课程
   • 分数高于s1，或分数相同但student_id更大（匹配预期结果中分数相同时大ID靠前的规则）
3. 统计结果加1即为当前记录的排名——因为有N条记录比它靠前，它的排名就是N+1
4. 最后按要求的排序规则输出结果

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