牛客SQL136——每类试卷得分前3名

最新推荐文章于 2025-08-12 22:09:30 发布

原创最新推荐文章于 2025-08-12 22:09:30 发布 · 295 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

牛客专栏收录该内容

33 篇文章

订阅专栏

该文描述了一个SQL问题，旨在通过多步骤查询找出每个类别试卷中得分最高的前三名用户。首先，从考试记录中提取每个用户每类试卷的最高和最低分。接着，基于得分进行排序并分配排名。最后，选择排名在前3的用户信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

题目描述
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca

思路：找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者
1.先选出每个人每类的最高和最低分

select e.tag,r.uid,max(r.score) max_score,min(r.score) min_score
from exam_record as r join examination_info as e on r.exam_id=e.exam_id
where r.score is not null
group by e.tag,r.uid

2.在根据每个试卷进行排序

select *,ROW_NUMBER() over(partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) ranking
from (
	select e.tag,r.uid,max(r.score) max_score,min(r.score) min_score
	from exam_record as r join examination_info as e on r.exam_id=e.exam_id
	where r.score is not null
	group by e.tag,r.uid
) as t

3.再选出前三

select tag,uid,ranking
from(
    select *,ROW_NUMBER() over(partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) ranking
    from (
        select e.tag,r.uid,max(r.score) max_score,min(r.score) min_score
        from exam_record as r join examination_info as e on r.exam_id=e.exam_id
        where r.score is not null
        group by e.tag,r.uid
    ) as t
)as u
where ranking<=3