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

#select tid,uid,ranking from (
#(select tag as tid,uid,row_number() over (partition by tag order by score DESC ) as ranking  from examination_info as i join exam_record as r on i.exam_id=r.exam_id where tag='SQL' ) 
#union all
#(select tag as tid,uid,row_number() over (order by score desc)  from examination_info as i join exam_record as r on i.exam_id=r.exam_id where tag='算法' ))as t where ranking<=3

select tag,uid,ranking from
(select tag,uid,row_number() over(partition by tag order by max_score desc,min_score desc,uid desc) as ranking from
(select uid,tag,max(score) as max_score,min(score) as min_score from exam_record join examination_info using(exam_id) group by uid,tag) as t_uid_tag_score
)as t_tag_uid_rank
where ranking<=3