分组查询取每组前n条记录实例

最新推荐文章于 2023-05-26 14:45:00 发布

逐兔郎

最新推荐文章于 2023-05-26 14:45:00 发布

阅读量9.8k

点赞数 5

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/zhutulang/article/details/48680379

版权

SQL 专栏收录该内容

1 篇文章

订阅专栏

本文介绍如何使用SQL查询语句来筛选出每个国家的前三名比赛成绩记录，包括两种实现方式及其查询结果展示。

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

假设有这样一张运动员比赛成绩表 tb_score

现在要求查询出每个国家的前三名的成绩记录，查询语句可以这样写：

1、

select t3.id,t3.country,t3.score 
from (select t1.*, (select count(*) from tb_score t2 where t1.score<=t2.score and t1.country=t2.country) as rownum 
			from tb_score t1) t3 
where rownum <=3 order by country,score DESC;

2、

select a.id,a.country,a.name,a.score from tb_score a 
where exists 
(select count(*) from tb_score where country = a.country and score > a.score having Count(*) < 3)
order by a.country,score DESC;

3、

select a.id,a.country,a.name,a.score from tb_score a 
where 
(select count(*) from tb_score where country = a.country and score > a.score )<3
order by a.country,score DESC;

查询结果如下：