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

-- 1.统计各用户关于不同tag得分的最高最低分，注意去掉重复值

with t as (
SELECT
    DISTINCT(er.uid),
    ei.tag,
    max( score ) over ( PARTITION BY uid,tag ) AS max_score,
    min( score ) over ( PARTITION BY uid,tag ) AS min_score
FROM
    exam_record er
    LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
    )

--3.利用排序情况，拿到各tag前3名

SELECT * from (

--2.统计各tag得分排序，并按照最高分、最低分、uid降序
select tag,
uid,
row_number() over(PARTITION by tag ORDER BY max_score desc,min_score desc,uid DESC) ranking
from t ) p
where ranking <=3