SQL180 每类试卷得分前3名

描述

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：

解释：有作答得分记录的试卷tag有SQL和算法，SQL试卷用户1001、1002、1003、1004有作答得分，最高得分分别为81、81、89、85，最低得分分别为78、81、86、40，因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。

SELECT
    tag,
    uid,
    ranking
FROM
    (
        SELECT
            ei.tag,
            er.uid,
            MAX(er.score) AS max_score, -- 每个用户在该类别下的最高分
            MIN(er.score) AS min_score, -- 最低分（用于平局决胜）
            ROW_NUMBER() OVER (
                PARTITION BY
                    ei.tag
                ORDER BY
                    MAX(er.score) DESC, -- 先按最高分排序
                    MIN(er.score) DESC, -- 再按最低分排序
                    er.uid DESC -- 最后按 uid 排序
            ) AS ranking
        FROM
            examination_info ei
            JOIN exam_record er USING (exam_id)
        WHERE
            er.score IS NOT NULL -- 只统计有得分的记录
        GROUP BY
            ei.tag,
            er.uid -- 按“类别+用户”分组
    ) t
WHERE
    ranking <= 3 -- 取前3名
ORDER BY
    tag,
    ranking;

窗口函数

1. 窗口函数基本格式

函数名() OVER (
    PARTITION BY 分组字段   -- 按什么分组（可选）
    ORDER BY 排序字段       -- 按什么排序（必选）
) 

👉 就像“分组 + 排序 + 计算”，但不改变行数！

2. 常用函数

3. 举个例子

ROW_NUMBER() OVER (
    PARTITION BY class 
    ORDER BY score DESC
)

意思：

• 每个班（class）单独排名
• 按分数从高到低排
• 返回排名：第1名、第2名…

4. 注意！

• 窗口函数不能直接在 WHERE 里用
• 要用的话，得套一层子查询

SELECT * FROM (
    SELECT ..., ROW_NUMBER() OVER (...) AS rn
    FROM table
) t
WHERE rn <= 3;  -- ✅ 这样才能用

✅ 一句话记住

PARTITION BY 分组，ORDER BY 排序，ROW_NUMBER() 给每行打上排名。