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

目录

• 前言
• 注意事项
• 准备SQL
• 错误查询
• 方法一
• 方法二（适用于自增ID和创建时间排序一致）
• 方法三（适用于自增ID和创建时间排序一致，查询性能最优）
• 方法四（通过DISTINCT关键字打破MySQL语句优化使排序生效）
• 方法五（以创建时间为基准获取每个用户最新的一条数据，必须要添加对应字段的索引 最好是覆盖索引）
• 总结
• MAX()函数和MIN()这一类函数和GROUP BY配合使用存在问题

select @@sql_mode;

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

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 `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 1, '100', '100', 1670300656630);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 1, '-10', '90', 1670300656640);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 1, '5', '95', 1670300656650);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 3, '998', '998', 1670300656660);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 3, '-100', '898', 1670300656670);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 3, '-98', '800', 1670300656680);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 2, '666', '666', 1670300656690);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 2, '-66', '600', 1670300656695);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (null, 2, '-600', '0', 1670300656699);

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

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

SELECT
    *
FROM
    customer_wallet_detail 
WHERE
    id IN ( SELECT MAX( id ) FROM customer_wallet_detail GROUP BY customer_id ) 
ORDER BY
    customer_id;

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

SELECT
    * 
FROM
    ( SELECT DISTINCT * FROM `customer_wallet_detail` ORDER BY id DESC ) AS t1 
GROUP BY
    t1.customer_id;

SELECT
    b1.* 
FROM
    customer_wallet_detail t1 
WHERE
    ( SELECT COUNT( 1 ) FROM customer_wallet_detail t1 WHERE t2.customer_id = t1.customer_id AND t1.create_time <= t2.create_time ) <= 1;