SQL180 每类试卷得分前3名
描述
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
| id | exam_id | tag | difficulty | duration | release_time |
| 1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 2 | 9002 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2021-09-01 10:00:00 |
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
| id | uid | exam_id | start_time | submit_time | score |
| 1 | 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 78 |
| 2 | 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
| 3 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
| 4 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
| 5 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
| 6 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
| 7 | 1005 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
| 8 | 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 84 |
| 9 | 1003 | 9003 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
| 10 | 1003 | 9002 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：
tag | uid | ranking |
| SQL | 1003 | 1 |
| SQL | 1004 | 2 |
| SQL | 1002 | 3 |
| 算法 | 1005 | 1 |
| 算法 | 1006 | 2 |
| 算法 | 1003 | 3 |
解释：有作答得分记录的试卷tag有SQL和算法，SQL试卷用户1001、1002、1003、1004有作答得分，最高得分分别为81、81、89、85，最低得分分别为78、81、86、40，因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。
SELECTtag,uid,ranking
FROM(SELECTei.tag,er.uid,MAX(er.score) AS max_score, -- 每个用户在该类别下的最高分MIN(er.score) AS min_score, -- 最低分（用于平局决胜）ROW_NUMBER() OVER (PARTITION BYei.tagORDER BYMAX(er.score) DESC, -- 先按最高分排序MIN(er.score) DESC, -- 再按最低分排序er.uid DESC -- 最后按 uid 排序) AS rankingFROMexamination_info eiJOIN exam_record er USING (exam_id)WHEREer.score IS NOT NULL -- 只统计有得分的记录GROUP BYei.tag,er.uid -- 按“类别+用户”分组) t
WHEREranking <= 3 -- 取前3名
ORDER BYtag,ranking;窗口函数
1. 窗口函数基本格式
函数名() OVER (PARTITION BY 分组字段 -- 按什么分组（可选）ORDER BY 排序字段 -- 按什么排序（必选）
) 👉 就像“分组 + 排序 + 计算”，但不改变行数！
2. 常用函数
| 函数 | 作用 |
|---|---|
ROW_NUMBER() | 排名：1,2,3,4…（不并列） |
RANK() | 排名：1,2,2,4…（并列，跳号） |
DENSE_RANK() | 排名：1,2,2,3…（并列，不跳号） |
3. 举个例子
ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC
)意思：
- 每个班（class）单独排名
- 按分数从高到低排
- 返回排名：第1名、第2名…
4. 注意！
- 窗口函数不能直接在
WHERE里用 - 要用的话，得套一层子查询
SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (...) AS rnFROM table
) t
WHERE rn <= 3; -- ✅ 这样才能用✅ 一句话记住
PARTITION BY分组，ORDER BY排序，ROW_NUMBER()给每行打上排名。