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

-- 构造 最大分数 和 最小分数 临时表
with table1 as (
    select uid,tag , 
    max(score) as max_score , min(score) as min_score
    from exam_record left join examination_info using(exam_id)
    where score is not null
    group by uid,tag
),
table2 as (
select tag , uid , 
-- 根据 tag 分组，按照分数降序
row_number() over(partition by tag order by max_score desc,min_score desc, uid desc) as ranking 
from table1
-- 筛选出 前3个
# where ranking <=3   
-- ！！  需要特别这注意的是在 SQL 查询中，你不能在 WHERE 子句中直接引用窗口函数的别名 'ranking'，因为窗口函数是在查询的结果集中最后计算的，而 WHERE 子句是在数据筛选之前执行的。
)
select *
from table2
where ranking <=3