MySQL查询top N记录
2024-08-30 04:38:22
下面以查询每门课程分数最高的学生以及成绩为例,演示如何查询 top N记录。下图是测试数据,表结构和相关 insert 脚本见《常用SQL之日期格式化和查询重复数据》。
使用自连接【推荐】
select a.name,a.course,a.score from test1 a,
(select course,max(score) score from test1 group by course) b
WHERE a.course=b.course and a.score=b.score;
执行后,结果集如下:
使用相关子查询
select name,course,score from test1 a
where a.score=(select max(score) from test1 where a.course=test1.course); 或者 select name,course,score from test1 a
where not exists(select 1 from test1 where a.course=course and a.score < score); 或者 select a.* from test1 a
where 1>(select count(*) from test1 where course=a.course and score>a.score);
结果集同上图。需要注意的是如果最高分有多条,会全部查出!
TOP N(N>1)
以N=2为例,演示如何查询TOP N(N>1)。
使用union all
如果结果集比较小,可以用程序查询单个分组结果后拼凑,也可以使用 union all。
(select name,course,score from test1 where course='语文' order by score desc limit 2)
union all
(select name,course,score from test1 where course='数学' order by score desc limit 2)
union all
(select name,course,score from test1 where course='英语' order by score desc limit 2);
自身左连接
select a.name,a.course,a.score
from test1 a left join test1 b on a.course=b.course and a.score<b.score
group by a.name,a.course,a.score
having count(b.id)<2
order by a.course,a.score desc;
两个表做连接查询,笛卡尔积,然后用having count(b.id)筛选出比当前条数大的条数。
自关联+count()
select a.* from test1 a
where 2>(select count(*) from test1 where course=a.course and score>a.score)
order by a.course,a.score desc;
思路就是判断每一条记录,比a中当前记录大的条数是否为2,如果有2条比较大,则符合。筛选出全部记录,最后按课程和学分排序。但是子查询进行了n次count(*)计算,因此性能极差。
半连接+count()+having
select * from test1 a where exists(select count(*) as sum from
test1 b where b.course=a.course and b.score>a.score having sum <2)
order by a.course,a.score desc;
最新文章
- PHP ";万能";输出随机字符串
- 使用Python给要素添加序号
- spring实战三装配bean之Bean的作用域以及初始化和销毁Bean
- 搭建企业cacti服务器
- 2016 Al-Baath University Training Camp Contest-1 C
- 通过 Mesos、Docker 和 Go,使用 300 行代码创建一个分布式系统
- POJ 1607
- 关于一道简单的Java 基础面试题的剖析: short s1=1;s1 = s1 +1会报错吗?
- HDU 5623 KK&#39;s Number (博弈DP)
- 简单的三方登录SDK示例,Android Activity之间数据的传递
- [转]iptables
- OAuth2.0学习(1-9)新浪开放平台微博认证-web应用授权(授权码方式)
- C# 实体类转json数据过滤掉字段为null的字段
- 简说raid1 raid2 raid5 raid6 raid10的优缺点和做各自raid需要几块硬盘
- 转:纯CSS实现“鼠标移过显示层”效果
- How to understand three foundanmental faults?
- 如何更新world文档的目录
- float类型如何转换为string类型
- 乱码字符引起的JSON转换失败
- 「6月雅礼集训 2017 Day8」route