SQL27 每类试卷得分前3名

题目主要信息：

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

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

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

问题拆分：

• 筛选出一个各类标签与用户及排名的表格：

  • 标签信息和得分信息分布在两个表格，需要将其用exam_id连接在一起。知识点：join...on...
  • 排名是以每个标签每个用户为组的，因此要分组。group by tag, e_r.uid 知识点：group by
  • 对每类标签使用分组聚合排名。知识点：row_number() over partition by 排名优先级先是每个用户的最大得分降序，然后是每个用户的最低得分降序，最后用户ID降序。知识点：order by、min()、max()

• 从上述表格中选出排名小于等于3的标签、用户ID及排名。知识点：select...from...where...

代码：

select tag, uid, ranking
from(
    select tag, e_r.uid,
    row_number() over (partition by tag order by tag, max(score) desc, min(score) desc, e_r.uid desc) as ranking
    from exam_record e_r join examination_info e_i
    on e_r.exam_id = e_i.exam_id
    group by tag, e_r.uid
)ranktable
where ranking <= 3