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

#1.以作答表为主表进行连接两张表，找出每类试卷每类用户（group by tag, uid)的最大分数和最小分数，在后面加两列

with c as (select b.tag,a.uid,max(a.score) max_score,min(a.score) min_score

group by b.tag,a.uid),

#2.按照规则加一列ranking！

    row_number() over(partition by tag order by c.max_score desc, c.min_score desc,uid desc) ranking

#完整：

with c as (select b.tag,a.uid,max(a.score) max_score,min(a.score) min_score
from exam_record a left join examination_info b on a.exam_id=b.exam_id
group by b.tag,a.uid),
    d as (select c.tag,c.uid,c.max_score,c.min_score,
row_number() over(partition by tag order by c.max_score desc, c.min_score desc,uid desc) ranking
from c)
select tag,uid, ranking 
from d 
where ranking <=3;