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

SELECT tid, uid, ranking
FROM(
    SELECT tag AS tid, uid, ROW_NUMBER() OVER (PARTITION BY tag ORDER BY max_score DESC, min_score DESC, uid DESC) AS ranking
    FROM (SELECt uid, tag, MIN(score) AS min_score, MAX(score) AS max_score
    FROM exam_record AS er LEFT JOIN examination_info AS ei ON er.exam_id = ei.exam_id
    GROUP BY uid, tag) AS t1
) AS t2
WHERE ranking <= 3;

最内层表1: 根据uid, tag对左连接后的表进行聚合，分别得到每个(uid,tag)的最小成绩(min_score)和最大成绩(max_score)列

次内层表2: 对tag分组进行排序，以最大成绩(max_score)降序，最小成绩(min_score)降序，uid 降序进行排序

最外层表3: 在选取tid(tag), uid, ranking的前三名