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

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

在开发报表功能时，经常会遇到需要根据某个字段分组查询每组最新一条记录的需求。本文以一个实际案例为基础，详细介绍了在MySQL中实现这一需求的三种方法，并对每种方法的优缺点进行了分析。

前言

在开发报表功能时，经常会遇到需要根据某个字段分组查询每组最新一条记录的需求。本文以一个实际案例为基础，详细介绍了在MySQL中实现这一需求的三种方法，并对每种方法的优缺点进行了分析。

注意事项

• 数据库版本：MySQL 5.7+
• 执行 GROUP BY 语句时可能会遇到 sql_mode=only_full_group_by 的问题，可以通过以下方式解决：

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

为了便于演示，我们创建一个模拟的用户钱包明细表：

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

错误查询

尝试使用子查询和 GROUP BY 查询最新一条数据时，发现查询结果总是最旧的一条数据：

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

错误原因

在MySQL 5.7及之后的版本中，如果 GROUP BY 的子查询中包含 ORDER BY，但 GROUP BY 不与 LIMIT 配合使用，ORDER BY 会被忽略掉。

方法一

通过添加 LIMIT 条件来实现功能：

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

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

使用 MAX() 函数来实现该功能：

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 查询替换为连接查询：

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

总结

结合业务测试结果，方法三是最合适的，SQL简单且性能适中。方法一虽然可行但实现较为复杂，方法二在大数据量下性能较差。最终选择哪个方案主要取决于具体业务场景。

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

MAX()函数和MIN()这一类函数和GROUP BY配合使用时，可能会导致查询结果不准确。正确的查询方式应该确保分组后的数据与聚合函数的结果对应一致。

正确查询：

错误查询：