Mysql分组查询每组最新一条数据的三种实现方法
作者:kerwin_code 时间:2024-01-13 16:32:04
前言
在写报表功能时遇到一个需要根据用户id分组查询最新一条钱包明细数据的需求,在写sql测试时遇到一个有趣的问题,开始使用子查询根据时间倒序+group by customer_id发现查询出来的数据一直都是最旧的一条,而不是我需要的最新一条数据我明明已经倒序排了,后来总结出了三种解决方案如下。
注意事项
数据库版本 Mysql5.7+
执行 GROUP BY 语句的时候出现 sql_mode=only_full_group_by 解决方法(这里是Mysql8的解决方案,Mysql5.7也差不多自行百度即可)
1、执行 select @@sql_mode; 查看sql模式
select @@sql_mode;
2、将sql_mode中的only_full_group_by模式剔除 重新设置sql_mode值,如果是使用JDBC连接需要重启项目才能生效。
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
准备SQL
这里模拟一个sql
DROP TABLE IF EXISTS `customer_wallet_detail`;
CREATE TABLE `customer_wallet_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) NULL DEFAULT NULL COMMENT '用户ID',
`happen_amount` varchar(15) NULL DEFAULT '0' COMMENT '发生金额 带'-'号的代表扣款',
`balance_amount` varchar(15) NULL DEFAULT '0' COMMENT '可用余额',
`create_time` bigint(20) NULL DEFAULT NULL COMMENT '发生时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '用户钱包明细' ;
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (1, 1, '100', '100', 1670300656630);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (2, 1, '-10', '90', 1670300656640);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (3, 1, '5', '95', 1670300656650);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (4, 3, '998', '998', 1670300656660);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (5, 3, '-100', '898', 1670300656670);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (6, 3, '-98', '800', 1670300656680);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (7, 2, '666', '666', 1670300656690);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (8, 2, '-66', '600', 1670300656695);
INSERT INTO `test`.`customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `happen_time`) VALUES (9, 2, '-600', '0', 1670300656699);
错误查询
SELECT
*
FROM
( SELECT * FROM customer_wallet_detail ORDER BY create_time DESC ) t1
GROUP BY
t1.customer_id;
错误原因
在mysql5.7以及之后的版本,如果GROUP BY的子查询中包含ORDER BY,但是 GROUP BY 不与 LIMIT 配合使用,ORDER BY会被忽略掉,所以子查询在 GROUP BY 时排序不会生效,可能是因为子查询大多数是作为一个结果给主查询使用,所以子查询不需要排序。
方法一
鉴于以上的原因我们可以添加上 LIMIT 条件来实现功能。
PS:这个LIMIT的数量可以先自行 COUNT 出你要遍历的数据条数(这个数据条数是所有满足查询条件的数据合,我这里共9条数据)
SELECT
*
FROM
( SELECT * FROM customer_wallet_detail ORDER BY create_time DESC ) t1
GROUP BY
t1.customer_id;
方法二(适用于自增ID和创建时间排序一致)
方法一需要先 COUNT 查询然后将查询结果设置到 LIMIT 条件中比较麻烦,这里还可以使用 MAX() 函数来实现该功能。
PS:因为我这里的业务数据是有序插入的,使用主键自增id和create_time结果是一样的而且使用id查询效率更高,如果没有唯一且有序的id可以替代create_time那么就用方案一,不能直接使用 SELECT id,MAX(create_time) 这种操作来获取最新一条数据id原因在总结中有详细描述。
SELECT
*
FROM
customer_wallet_detail
WHERE
id IN ( SELECT MAX( id ) FROM customer_wallet_detail GROUP BY customer_id )
ORDER BY
customer_id;
方法三(适用于自增ID和创建时间排序一致)
方法三和方法二实现逻辑基本一致只是将IN查询替换成了连接查询,本地20w条数据测试 方法三比方法二性能提升50%,有兴趣的可以增大数据集测试后续性能变化。
SELECT
t1.*
FROM
customer_wallet_detail t1
INNER JOIN ( SELECT MAX( id ) AS id FROM customer_wallet_detail GROUP BY customer_id ) t2 ON t1.id = t2.id
来源:https://blog.csdn.net/weixin_44606481/article/details/128200727
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
使用limit,offset分页场景时为什么会慢
![](https://img.aspxhome.com/file/2023/8/71788_0s.png)
pytorch 数据加载性能对比分析
PyCharm使用最多也最常用默认快捷键分享
![](https://img.aspxhome.com/file/2023/9/81579_0s.png)
MySQL 有关MHA搭建与切换的几个错误log汇总
![](https://img.aspxhome.com/file/2023/0/108880_0s.png)
Golang 操作TSV文件的实战示例
基于python制作简易版学生信息管理系统
![](https://img.aspxhome.com/file/2023/5/112745_0s.jpg)
MySQL使用Partition功能实现水平分区的策略
Javascript中的基本类型和引用类型概述说明
![](https://img.aspxhome.com/file/2023/0/136370_0s.png)
Qt操作SQLite数据库的教程详解
![](https://img.aspxhome.com/file/2023/5/103085_0s.png)
详解Pycharm出现out of memory的终极解决方法
![](https://img.aspxhome.com/file/2023/0/112780_0s.png)
手残删除python之后的补救方法
![](https://img.aspxhome.com/file/2023/7/115757_0s.jpg)
Python实现炸金花游戏的示例代码
教你怎么用PyCharm为同一服务器配置多个python解释器
![](https://img.aspxhome.com/file/2023/6/131096_0s.gif)
在python 中实现运行多条shell命令
Python中pandas dataframe删除一行或一列:drop函数详解
mysql慢查询日志轮转_MySQL慢查询日志实操
![](https://img.aspxhome.com/file/2023/5/69775_0s.png)
python使用time、datetime返回工作日列表实例代码
解析arp病毒背后利用的Javascript技术
使用Python制作微信跳一跳辅助
![](https://img.aspxhome.com/file/2023/6/121086_0s.png)
MySQL常见错误有哪些_MySQL常见错误的快速解决方法
![](https://img.aspxhome.com/file/2023/0/83370_0s.png)