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

/*
 试卷信息表 examination_info
 试卷作答记录表 exam_record
 
 找到每类试卷得分的前3名，
 如果两人最大分数相同，选择最小分数大者，
 如果还相同，选择uid大者。
 */
select
    tid,
    uid,
    ranking
from
    (
        select
            b.tag as tid,
            a.uid,
            row_number() over(partition by b.tag order by max(a.score) desc,min(a.score) desc,a.uid desc) as ranking
        from
            exam_record a
            left join examination_info b using (exam_id)
        where
            a.score is not null
        group by
            b.tag,
            a.uid
    ) a
where
    ranking <= 3