题解 | #每类试卷得分前3名#
每类试卷得分前3名
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca
# 找到每类试卷得分的前3名,如果两人最大分数相同,选择最小分数大者,如果还相同,选择uid大者 SELECT * FROM( SELECT a.tag, a.uid, ROW_NUMBER() OVER (PARTITION BY a.tag ORDER BY max_score DESC, min_score DESC, uid DESC) AS ranking FROM( SELECT i.tag, r.uid, MAX(r.score) AS max_score, MIN(r.score) AS min_score FROM examination_info AS i LEFT JOIN exam_record AS r ON i.exam_id=r.exam_id GROUP BY i.tag, r.uid ) AS a ) AS b WHERE b.ranking<=3;