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

with temp as(
select
    a.uid,
    b.tag as tid,
    max(score) as max,
    min(score) as min
from exam_record as a 
left join examination_info as b 
on a.exam_id = b.exam_id
group by 
    a.uid,
    b.tag
)

select 
    tid,
    uid,
    flag as ranking
from
(select 
    tid,
    uid,
    rank() over (partition by tid order by max desc, min desc, uid desc) as flag
from temp
) as a 
where flag <= 3