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

select 
    tt.tid
  , tt.uid
  , tt.ranking
from (
    select 
      t1.tag as tid
    , t2.uid as uid
    # 如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者
    , row_number() over(partition by t1.tag order by max(t2.score) desc, min(t2.score) desc, t2.uid desc ) as ranking
    from examination_info t1
    left join exam_record t2
        on t2.exam_id = t1.exam_id
    group by t1.tag, t2.uid
   ) tt
where tt.ranking <= 3  # 前三名
/*SQL*/;