一、表、字段

examination_info (试卷信息表) id (自增ID) exam_id (试卷ID) tag (类别标签) difficulty (难度) duration (时长) release_time (发布时间)

exam_record (试卷作答记录表) id (自增ID) uid (用户ID) start_time (开始时间) exam_id (试卷ID) submit_time (提交时间) score (得分)

二、条件

1、找到每类试卷得分的前3名

使用left...join...on 将两个表连接 起来 然后根据tag类别标签和uid用户ID进行分组

select 
	te.tag,
    tem.uid 
from 
	examination_info te 
left join 
	exam_record tem 
on 
	te.exam_id = tem.exam_id   
group by 
	te.tag,tem.uid 

2、如果两人最大分数相同，选择最小分数大者

3、如果还相同，选择uid大者

是根据类别标签进行窗口分组 也是根据类别标签窗口排序 遇到有重复的 先按照最大分数排序、依次按照最小分数、uid排序 所以 条件如下:

利用row_number()over(partition by ...order by....) 进行排名 排序 row_number开窗函数排序规则是 遇到有重复的数据 比如1、2、3、4的规则进行排名、排序 rank()排序、排名规则是 遇到有重复数据的 比如 1,、2、2、4 row_number()排序、排名排名规则是 遇到有重复数据的 比如1、2、2、3 如果不太明白开窗函数的 可看这篇文章 https://blog.csdn.net/nanyangnongye/article/details/122322488

row_number()
over(partition by tag  order by tag,
     max(score) desc,
     min(score) desc,
     uid desc)

所以 sql组装如下:

select 
	te.tag,
    tem.uid,
    row_number()
	over(partition by tag  order by tag,
    max(score) desc,
    min(score) desc,
    uid desc) as ranking
from 
	examination_info te 
left join 
	exam_record tem 
on 
	te.exam_id = tem.exam_id   
group by 
	te.tag,tem.uid 

三、最终题解

但是由于开窗函数后面不能直接跟having 即 不能使用包含窗口函数的表达式的别名 所以将组装sql 使用with 表名 as()临时表的方式 进行条件筛选

with temp as(
select 
	te.tag,
    tem.uid,
    row_number()
	over(partition by tag  order by tag,
    max(score) desc,
    min(score) desc,
    uid desc) as ranking
from 
	examination_info te 
left join 
	exam_record tem 
on 
	te.exam_id = tem.exam_id   
group by 
	te.tag,tem.uid 
)select * from temp where ranking <= 3