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

select tag,uid,ranking from 
(select t2.tag,t1.uid,
row_number()over(partition by t2.tag order by max(t1.score) desc,
min(t1.score) desc,max(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 t2.tag,t1.uid) as t3
where ranking <4

新知识点：

1. group by 后边加多个字段，参考该博客https://blog.csdn.net/qq_47699076/article/details/127946512
2. 开窗函数：row_number()over(partition by 'col' order by 'col1,col2'.),开窗函数和聚合函数的区别：根据group by+字段把字段聚成一组，然后对改组进行操作，而partition by是把字段聚成一组，但是是按照每一条数据进行操作。