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

with tmp as
(
select 
distinct 
tag tid,
uid,
-- score,
max(score) over(partition by tag,uid ) max_score,
min(score) over(partition by tag,uid ) min_score
from 
exam_record a left join
examination_info b
on a.exam_id=b.exam_id
where score is not null
)

select 
*
from 
(
select 
tid,
uid,
row_number() over (partition by tid order by max_score desc,min_score desc,uid desc) ranking
from
tmp
) a
where a.ranking <=3

知识点

使用窗口函数之后取TOPN，要用子查询，固定的写法

select *
       from (select *,row_number() over(partition by 姓名 order by 成绩 desc)as ranking
                    from 各科成绩表) as a
       where ranking <= 2;