题解 | #每类试卷得分前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 tag,uid,MAX(score) as max_score,MIN(score) as min_score
FROM exam_record e1
LEFT JOIN examination_info e2
ON e1.exam_id=e2.exam_id
GROUP BY uid,tag) a) b
WHERE ranking<=3;