Mysql分組查詢?nèi)∽钚碌膸追N方案總結(jié)

更新時間：2023年05月27日 11:51:47 作者：lianshanchi

在寫報表功能時遇到一個需要根據(jù)用戶id分組查詢最新一條錢包明細數(shù)據(jù)的需求,下面這篇文章主要給大家總結(jié)介紹了關(guān)于Mysql分組查詢?nèi)∽钚碌膸追N方案,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

1.row_number函數(shù) (mysql8以上支持)

示例：

SELECT *
FROM
( SELECT *, row_number ( ) over ( PARTITION BY 分組字段 ORDER BY 排序字段 DESC ) AS rn FROM 表 ) pca
WHERE
pca.rn = 1

解釋：

1. rn字段：返回的是分組內(nèi)的結(jié)果排序依次遞增

2. 分組字段：進行分組的字段

3. 排序字段：需要取最新邏輯的字段

注意：

row_number函數(shù)得到的字段rn不能在表后面直接加where條件過濾，需要用select包裝生成臨時表pca 再進行取最新過濾
并且該函數(shù)只能在mysql8.0版本以上運行，5.7不支持該函數(shù)

2.子查詢

示例：

SELECT *
FROM
表 a
WHERE
a.排序字段 = ( SELECT max( b.排序字段 ) FROM 表 b WHERE b.分組字段 = a.分組字段 )
GROUP BY
a.分組字段

解釋：

分組字段：進行分組的字段排序字段：嚴格意義上不叫排序字段，可以在當前sql語句中可以認為是在分組后需要找最新值字段

注意：

該子查詢性能屬于四種方案中最低，不適合大數(shù)據(jù)量查詢使用，

最后GROUP BY分組字段是為了在多個最大值有多個相同數(shù)據(jù)情況下去重處理按業(yè)務場景可去掉

3.臨時表

示例：

創(chuàng)建臨時表
CREATE TEMPORARY TABLE tmp_表 as select 分組字段,max(排序字段)as 排序字段 from 表 group by 排序字段;
關(guān)聯(lián)臨時表查詢
select * from 表 p join tmp_表 tmp on tmp.分組字段= p.分組字段 and tmp.排序字段= p.排序字段

解釋：

分組字段：進行分組的字段

排序字段：嚴格意義上不叫排序字段，可以在當前sql語句中可以認為是在分組后需要找最新值字段

注意：

先查詢出最新的結(jié)果存入臨時表，再進行聯(lián)表查詢，數(shù)據(jù)量大一點的情況下可以對創(chuàng)建完的臨時表加索引，使整個過程的復雜度趨近f(n)。

4.新增字段標識

從業(yè)務層面優(yōu)化，在表上加一個字段，isnewdata,bit型或者int型就好，每次在表中提交數(shù)據(jù)時，事務中先將要提交的數(shù)據(jù)所涉及的id，將歷史數(shù)據(jù)isnewdata=1的更新為0，新提交的數(shù)據(jù)，isnewdata為1，然后增加一個索引isnewdata，或者如果需要和其他表關(guān)聯(lián)的時候，增加復合索引，性能一下就上來了。

5.使用mysql用戶變量提供排序序列號

示例：

SET @rank := 0;
SET @cgroup := NULL;
SELECT
a.*
IF
( @cgroup = a.分組字段, @rank := @rank + 1, @rank := 1 ) AS rank_no,
@cgroup := a.分組字段,
FROM
表 a
ORDER BY
a.分組字段,
a.排序字段 ASC

解釋：

分組字段：進行分組的字段
排序字段：分組后排序的字段
@rank ：序號計數(shù)變量
@cgroup ：分組字段暫存變量，用來比較是否進入了下一組數(shù)據(jù)

運行邏輯：

該sql首先會進行排序，先分組字段，再排序字段，這點很關(guān)鍵，然后根據(jù)mysql的service層執(zhí)行順序?qū)φ故緮?shù)據(jù)進行處理，先進入if函數(shù)判斷數(shù)據(jù)是累加還是初始化@rank用戶變量（注意首先第一次進來@cgroup為空），然后將分組字段的值賦予@cgroup變量。整個執(zhí)行過程非常精巧有意思，得細細品味。

注意：

不支持開窗函數(shù)的mysql版本可以使用該方式，但是要ORM支持多行SQL代碼塊才行，還要修改數(shù)據(jù)庫的SQL_MODE，mybatis和JPA都可以，Oracle不行需要配置。比如mycat這種數(shù)據(jù)庫中間件

附：MySQL 取出每個分組中最新的一條數(shù)據(jù)（ID最大）

場景：由于一個攝像頭管理一個范圍，且管理的某個人可以多次犯規(guī)。故，一個攝像頭可以上報有多個事件，多個事件可能同時上報，可能有先后順序。

需求：現(xiàn)地圖只顯示有事件攝像頭的最新一條事件信息，故，需要ID倒序后，以攝像頭分組，以此獲得攝像頭最新的一條事件信息。（這里以ID倒序而不以創(chuàng)建時間倒序，是有一定優(yōu)勢的）。

思路：先ID（時間）倒序，后分組。

說明：如果是單表查詢，默認分組后是ID最大的一條數(shù)據(jù)，若是連表查詢，需要以下方法處理。

SELECT t.id,t.camera_id,t.create_time FROM 
    (SELECT e.* FROM sys_enforce_event e LEFT JOIN sys_dict_node n ON e.node_id = n.id 
    WHERE e.is_deleted = 0 AND e.source_id = 2 AND n.`code` IN (200,301,302,400,500) AND e.camera_id IS NOT NULL ORDER BY e.id DESC ) t    
GROUP BY t.camera_id

以下方式就不對了，查找的不是最新一條記錄（連表查詢，看清區(qū)別）

SELECT e.* (SELECT * FROM sys_enforce_event ORDER BY id DESC ) e LEFT JOIN sys_dict_node n ON e.node_id = n.id
    WHERE e.is_deleted = 0 AND e.source_id = 2 AND n.`code` IN (200,301,302,400,500 ) AND e.camera_id IS NOT NULL     
GROUP BY e.camera_id

注意事項：此方式僅支持低于5.7版本的MySql（SELECT VERSION();）

關(guān)于高于5.7版本的，可在排序后添加l imit 999999，即可為最新一條。