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. 分組字段：進(jìn)行分組的字段

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

注意：

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

2.子查詢

示例：

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

解釋：

分組字段：進(jìn)行分組的字段排序字段：嚴(yán)格意義上不叫排序字段，可以在當(dāng)前sql語句中可以認(rèn)為是在分組后需要找最新值字段

注意：

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

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

3.臨時(shí)表

示例：

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

解釋：

分組字段：進(jìn)行分組的字段

排序字段：嚴(yán)格意義上不叫排序字段，可以在當(dāng)前sql語句中可以認(rèn)為是在分組后需要找最新值字段

注意：

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

4.新增字段標(biāo)識(shí)

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

5.使用mysql用戶變量提供排序序列號(hào)

示例：

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

解釋：

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

運(yùn)行邏輯：

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

注意：

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

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

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

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

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

說明：  如果是單表查詢，默認(rèn)分組后是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 

以下方式就不對(duì)了，查找的不是最新一條記錄（連表查詢，看清區(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 

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

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

總結(jié)：

如果業(yè)務(wù)數(shù)據(jù)量不大，最多就幾萬條，用方案1，2，3均可。數(shù)據(jù)量不超過100萬行，方案1和3還能勉強(qiáng)頂住。超過100萬行，就要從業(yè)務(wù)層面去優(yōu)化了，此時(shí)選擇方案4是明智的

到此這篇關(guān)于Mysql分組查詢?nèi)∽钚碌膸追N方案總結(jié)的文章就介紹到這了,更多相關(guān)Mysql分組查詢?nèi)∽钚聝?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論