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

with a as(
    select  tag,er.uid,max(score) as da,min(score) as xiao
    from examination_info ei 
    join exam_record er on ei.exam_id=er.exam_id
    group by tag,er.uid
)

select * from
(select tag,uid,
row_number() over (partition by tag order by a.da desc, xiao desc,uid desc) as ranking
from a )b
where ranking<=3

CTE算出最大最小后，主查询中用row number排序