文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Mysql分组查询每组最新一条数据的三种实现方法

2023-01-12 11:30

关注

前言

在写报表功能时遇到一个需要根据用户id分组查询最新一条钱包明细数据的需求,在写sql测试时遇到一个有趣的问题,开始使用子查询根据时间倒序+group by customer_id发现查询出来的数据一直都是最旧的一条,而不是我需要的最新一条数据我明明已经倒序排了,后来总结出了三种解决方案如下。

注意事项

1、执行 select @@sql_mode; 查看sql模式

select @@sql_mode;

Mysql分组查询每组最新一条数据的三种实现方法

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);

Mysql分组查询每组最新一条数据的三种实现方法

错误查询

SELECT
	* 
FROM
	( SELECT * FROM customer_wallet_detail ORDER BY create_time DESC ) t1 
GROUP BY
	t1.customer_id;

Mysql分组查询每组最新一条数据的三种实现方法

错误原因

在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;

Mysql分组查询每组最新一条数据的三种实现方法

方法二(适用于自增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;

Mysql分组查询每组最新一条数据的三种实现方法

方法三(适用于自增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

Mysql分组查询每组最新一条数据的三种实现方法

总结

结合我的业务经过测试,目前看来方案三是最合适的,sql简单性能适中,方案一比方案二性能更差而且实现麻烦,最终选择那个方案主要看业务而定。

MAX()函数和MIN()这一类函数和GROUP BY配合使用存在问题

MAX()函数和MIN()这一类函数和GROUP BY配合使用,GROUP BY拿到的数据永远都是这个分组排序最上面的一条,而MAX()函数和MIN()这一类函数会将这个分组中最大 | 最小的值取出来,这样会导致查询出来的数据对应不上。

正确查询:

Mysql分组查询每组最新一条数据的三种实现方法

错误查询:这里的确拿到每个分组最新创建时间了但是拿的数据id还是排序的第一条

Mysql分组查询每组最新一条数据的三种实现方法

Mysql分组查询每组最新一条数据的三种实现方法

到此这篇关于Mysql分组查询每组最新一条数据的三种实现方法的文章就介绍到这了,更多相关Mysql分组查询每组最新数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯