明确题意：

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者

问题分解：

• 统计每类试卷上每个用户的排名，生成子表 t_tag_uid_rank:
  • 统计每个用户在每类试卷上的最大最小分，生成子表 t_uid_tag_score ：
    • 关联试卷作答表和试卷信息表：exam_record JOIN examination_info USING(exam_id)
    • 按用户ID和试卷类别分组：GROUP BY uid, tag
    • 统计最大最小分：MAX(score) AS max_score, MIN(score) AS min_score
  • 计算排名，按类别分区按最大分、最小分、用户ID降序计算窗口内的排名：
    • ROW_NUMBER() over (PARTITION BY tag ORDER BY max_score DESC, min_score DESC, uid DESC) as ranking
• 筛选排名前三：WHERE ranking <= 3

细节问题：

• 表头重命名：as

完整代码：

SELECT tag, uid, ranking
FROM (
    SELECT tag, uid,
        ROW_NUMBER() over (
            PARTITION BY tag
            ORDER BY max_score DESC, min_score DESC, uid DESC
        ) as ranking
    FROM (
        SELECT uid, tag, MAX(score) AS max_score, MIN(score) AS min_score
        FROM exam_record JOIN examination_info USING(exam_id)
        GROUP BY uid, tag
    ) as t_uid_tag_score
) as t_tag_uid_rank
WHERE ranking <= 3;