题解 | #每类试卷得分前3名#
每类试卷得分前3名
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca
#1.以作答表为主表进行连接两张表，找出每类试卷每类用户（group by tag, uid)的最大分数和最小分数，在后面加两列
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),
#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;
#sql#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;
