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

比较顺序

1. 最大分数前三，按最大分数降序
2. 若最大分数相同，继续按对应最小分数降序
3. 若最小分数仍相同，按uid降序（uid不存在相同）

思路

1. 先找到每个用户在每类试卷的最大得分与最小得分
2. 对每类试卷进行分组，按最大分数降序、最小分数降序、uid降序得到唯一排序数
3. 筛选出排序数 <= 3的记录

代码（写法一）

-- 每类试卷得分的前3名
select
    tag as tid,
    uid,
    ranking
from (
    select
        uid,
        tag,
        row_number() over (partition by tag order by max_s desc, min_s desc, uid desc) as ranking
    from (
        select
            t1.uid, 
            t2.tag,
            max(t1.score) as max_s,
            min(t1.score) as min_s
        from exam_record as t1
            left join examination_info as t2 on t1.exam_id = t2.exam_id
        group by t1.uid, t2.tag
    ) as t
) as t
where ranking <= 3

代码（写法二）

• 是上面写法的简化版，都是先执行group by再执行OLAP函数

select tid, uid, ranking 
from (
    select
        t2.tag as tid,
        t1.uid,
        row_number() over (partition by t2.tag order by max(t1.score) desc, min(t1.score) desc, t1.uid desc) as ranking
    from exam_record as t1
        left join examination_info as t2 on t1.exam_id = t2.exam_id
    group by t1.uid, t2.tag
) as t
where ranking <= 3