mysql分组后,取每组第一条数据或最新一条

环境

MySQL:5.7
Java:1.8

SQL语句的写法:

select * from (select distinct(a.id) tid, a.* from template_detail a
               where a.template_id in (3, 4)
              order by a.id desc) tt
group by tt.template_id;

思路:先进行排序,然后再进行分组,获取每组的第一条。

通过子查询中的order by a.id desc就可以做到是每组最新一条,还是第一条。
通过实践,我们知道这种查询方式,与分组子查询相比性能慢了些,慢一点点

-- 这种查询方式更快一丢丢
SELECT bb.`detail`, bb.`id`,bb.`template_id` 
from `template_detail` bb 
		INNER JOIN 
		( 
		    SELECT MAX(`id`) id, `template_id` 
		    from `template_detail` GROUP BY `template_id` 
		) as tb on bb.`id` = tb.id

Q: 为什么要写distinct(a.id)呢?
A:防止合并的构造(derived_merge);

什么是derived_merge?

derived_merge指的是一种查询优化技术,作用就是把派生表合并到外部的查询中,提高数据检索的效率。这个特性在MySQL5.7版本中被引入,可以通过如下SQL语句进行查看/开启/关闭等操作。

上面虽然听起来感觉很牛逼的样子,但是实际情况是,这个新特性,不怎么受欢迎,容易引起错误。

可以在子查询中使用以下函数来进行关闭这个特性:

可以通过在子查询中使用任何阻止合并的构造来禁用合并,尽管这些构造对实现的影响并不明确。 防止合并的构造对于派生表和视图引用是相同的:
   1.聚合函数( SUM()MIN()MAX()COUNT()等)
   2.DISTINCT
   3.GROUP BY
   4.HAVING
   5.LIMIT
   6.UNION或UNION ALL
   7.选择列表中的子查询
   8.分配给用户变量
   9.仅引用文字值(在这种情况下,没有基础表)

子查询order by失效的场景

select * from (select a.* from template_detail a
               where a.template_id in (3, 4)
              order by a.id desc) tt
group by tt.template_id;

假设我们现在把distinct(a.id) tid, 去掉,会发现子查询(或者叫:临时表)中的order by a.id desc失效了。
为什么会这样呢?

原理分析:

我们这里使用了临时表排序,继而对其结果进行分组,结果显示失败,加了distinct(a.id) tid, 后结果正确,原因是因为临时表(派生表derived table)中使用order by且使其生效,必须满足三个条件:

  1. 外部查询禁止分组或者聚合
  2. 外部查询未指定having,HAVING, order by
  3. 外部查询将派生表或者视图作为from句中唯一指定源

不满足这三个条件,order by会被忽略。

一旦外部表使用了group by,那么临时表(派生表 derived table)将不会执行filesort操作(即order by 会被忽略),所以我在临时表中加了(distinct(a.id))。
加了之后就相当于关闭了该特性,所以也就生效了。

参考地址:

https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

分组查询取每组最新的数据(order by 和group by使用问题)

mysql分组后获取每个组排序后的第一条数据(整行)

Mysql取分组后的每组第一条数据

评论 14
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

山鬼谣me

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值