题解 | 每类试卷得分前3名

# 粒度：试卷
# 对每类型试卷得分排名
# 注意点：由于SQL有两个等级,需要先去重,所以要先进行表连结
select
tag
,uid
,ranking
from
(
    select
    tag
    ,uid
    ,row_number() over(partition by tag order by max_score desc, min_score desc, uid desc) ranking
    from
    (
        select
        tag
        ,uid
        ,max(score) max_score
        , min(score) min_score
        from
        exam_record er
        join 
        examination_info ei
        on er.exam_id = ei.exam_id
        group by tag, uid
    ) A
    group by tag, uid
) B
where ranking < 4