Mysql分組查詢每組最新一條數據的三種實現方法

更新時間：2023年01月12日 11:07:32 作者：kerwin_code

我們在開發(fā)中經常會遇到分組查詢最新數據的問題,下面這篇文章主要給大家介紹了關于Mysql分組查詢每組最新一條數據的三種實現方法,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

前言

在寫報表功能時遇到一個需要根據用戶id分組查詢最新一條錢包明細數據的需求，在寫sql測試時遇到一個有趣的問題，開始使用子查詢根據時間倒序+group by customer_id發(fā)現查詢出來的數據一直都是最舊的一條，而不是我需要的最新一條數據我明明已經倒序排了，后來總結出了三種解決方案如下。

注意事項

數據庫版本 Mysql5.7+
執(zhí)行 GROUP BY 語句的時候出現 sql_mode=only_full_group_by 解決方法（這里是Mysql8的解決方案，Mysql5.7也差不多自行百度即可）

1、執(zhí)行 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 '發(fā)生金額 帶'-'號的代表扣款',
  `balance_amount` varchar(15) NULL DEFAULT '0' COMMENT '可用余額',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT '發(fā)生時間',
  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和創(chuàng)建時間排序一致）

方法一需要先 COUNT 查詢然后將查詢結果設置到 LIMIT 條件中比較麻煩，這里還可以使用 MAX() 函數來實現該功能。

PS：因為我這里的業(yè)務數據是有序插入的，使用主鍵自增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和創(chuàng)建時間排序一致）

方法三和方法二實現邏輯基本一致只是將IN查詢替換成了連接查詢，本地20w條數據測試方法三比方法二性能提升50%，有興趣的可以增大數據集測試后續(xù)性能變化。

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