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

MySQL分组查询每组最新的一条数据

前言

在写报表功能时遇到一个需要根据用户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连接需要重启项目才能生效。setglobal sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';setsession sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

准备SQL

这里模拟一个sql

DROPTABLEIFEXISTS`customer_wallet_detail`;CREATETABLE`customer_wallet_detail`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`customer_id`bigint(20)NULLDEFAULTNULLCOMMENT'用户ID',`happen_amount`varchar(15)NULLDEFAULT'0'COMMENT'发生金额 带'-'号的代表扣款',`balance_amount`varchar(15)NULLDEFAULT'0'COMMENT'可用余额',`create_time`bigint(20)NULLDEFAULTNULLCOMMENT'发生时间',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCOMMENT='用户钱包明细';INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(1,1,'100','100',1670300656630);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(2,1,'-10','90',1670300656640);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(3,1,'5','95',1670300656650);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(4,3,'998','998',1670300656660);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(5,3,'-100','898',1670300656670);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(6,3,'-98','800',1670300656680);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(7,2,'666','666',1670300656690);INSERTINTO`test`.`customer_wallet_detail`(`id`,`customer_id`,`happen_amount`,`balance_amount`,`happen_time`)VALUES(8,2,'-66','600',1670300656695);INSERTINTO`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 ORDERBY create_time DESC) t1 
GROUPBY
    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 ORDERBY create_time DESCLIMIT9) t1 
GROUPBY
    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(SELECTMAX( id )FROM customer_wallet_detail GROUPBY customer_id )ORDERBY
    customer_id;

方法三（适用于自增ID和创建时间排序一致）

方法三和方法二实现逻辑基本一致只是将IN查询替换成了连接查询，本地20w条数据测试 方法三比方法二性能提升50%，有兴趣的可以增大数据集测试后续性能变化。

SELECT
    t1.*FROM
    customer_wallet_detail t1
    INNERJOIN(SELECTMAX( id )AS id FROM customer_wallet_detail GROUPBY customer_id ) t2 ON t1.id = t2.id

总结

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

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

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

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