在開發(fā)中經(jīng)常遇到這樣一類需求：取每種類型排名前幾的數(shù)據(jù)，在此我簡稱它為組內(nèi)排序泉坐。比如：

• 檢索論壇中某一版塊所有主題的最新一條帖子
• 查找所有會話中最新一條消息
• 查找一類商品的最新報價

這類問題的共同點(diǎn)是：需要按某個字段分組盈滴，且每組只能取一條記錄；按某個字段倒序古掏。

最近帜平，在做公司業(yè)務(wù)SQL查詢時，遇到一個有趣的問題：獲取各個訂單下更新時間最新的一條記錄序仙。每個訂單每更新一次突颊，都會根據(jù)更新內(nèi)容生成一條新的記錄。

舉例來說潘悼，有這樣一個表：

CREATE TABLE o_policy_orderrelation ( 
    id bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',
    orderid bigint NOT NULL default '0' COMMENT '訂單號',
    eventid bigint NOT NULL default '0' COMMENT '公共事件id',
    emergencyordertype tinyint NOT NULL default '0' COMMENT '應(yīng)急訂單類型律秃；1-行中受影響，2-即將出行受影響治唤，3-即將出行不受影響',
    datachange_lasttime timestamp(3)  NOT NULL default CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間',
    policyid bigint NOT NULL default '0' COMMENT '政策id',
    eventpolicyid bigint NOT NULL default '0' COMMENT '事件政策id',
    PRIMARY KEY (id), KEY ix_orderid (orderid), KEY ix_eventid (eventid), KEY ix_DataChange_LastTime (datachange_lasttime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='訂單事件關(guān)聯(lián)表';

數(shù)據(jù)庫中記錄如下：

id | orderid | eventid | emergencyordertype | datachange_lasttime | policyid | eventpolicyid
19 | 3062774559 | 2 | 0 | 2019-07-17 14:56:13.361 | 0 | 0
20 | 3062776520 | 2 | 0 | 2019-07-17 14:56:13.362 | 0 | 0
21 | 3062776519 | 2 | 0 | 2019-07-17 14:56:13.363 | 0 | 0
22 | 3062776517 | 2 | 0 | 2019-07-17 14:56:13.409 | 0 | 0
23 | 3062776515 | 2 | 0 | 2019-07-17 14:56:13.410 | 0 | 0
24 | 3062776514 | 2 | 1 | 2019-07-17 14:56:38.477 | 1 | 3
25 | 3062776514 | 2 | 2 | 2019-07-17 14:56:39.717 | 2 | 4
26 | 3062776514 | 2 | 3 | 2019-07-17 14:56:41.128 | 3 | 5
27 | 3062776727 | 111 | 1 | 2019-07-17 20:12:21.698 | 1 | 6

為了解決這個問題棒动，我想到了使用group by進(jìn)行訂單ID分組。然后在查詢時宾添，篩選出更新時間時間戳最大的數(shù)據(jù)船惨。SQL語句如下：

SELECT orderid, MAX(datachange_lasttime) AS max_time, eventpolicyid FROM o_policy_orderrelation GROUP BY orderid;

查詢結(jié)果如下：

id | orderid | max_time | eventpolicyid
19 | 3062774559 | 2019-07-17 14:56:13.361 | 0
24 | 3062776514 | 2019-07-17 14:56:41.128 | 3
23 | 3062776515 | 2019-07-17 14:56:13.410 | 0
22 | 3062776517 | 2019-07-17 14:56:13.409 | 0
21 | 3062776519 | 2019-07-17 14:56:13.363 | 0
20 | 3062776520 | 2019-07-17 14:56:13.362 | 0
27 | 3062776727 | 2019-07-17 20:12:21.698 | 6

我們可以看到orderid為3062776514的訂單在數(shù)據(jù)庫中有對應(yīng)的三條記錄，主鍵id分別為24,25,26缕陕。按道理我們查詢的該訂單ID對應(yīng)最近一條記錄是id=26粱锐，然而查詢出來對應(yīng)的記錄是id=24的eventpolicyid，即使max_time是最新的扛邑。

使用這條SQL確實(shí)幫我篩選到了各訂單最新的更新時間怜浅，但是無法篩選出最新更新時間對應(yīng)的記錄（其他列的值是隨機(jī)取的一條，具體選擇策略后面會介紹）蔬崩，因?yàn)镸ySQL默認(rèn)開啟了ONLY_FULL_GROUP_BY恶座，也就是說SELECT中的列只能是GROUP BY中出現(xiàn)的列。那么沥阳，如何篩選到各訂單更新時間最新的記錄呢跨琳？

解決辦法

臨時表關(guān)聯(lián)查詢

# [sql：查詢每個類別最新發(fā)表的那條記錄 - 個人文章 - SegmentFault 思否](https://segmentfault.com/a/1190000015864190)
SELECT a.* FROM o_policy_orderrelation AS a, (SELECT orderid, max(datachange_lasttime) AS max_time FROM o_policy_orderrelation GROUP BY orderid) AS b
WHERE a.orderid=b.orderid AND a.datachange_lasttime=b.max_time;

先查出每個orderid的最大的datachange_lasttime，查詢生成一個臨時表b：(SELECT orderid, max(datachange_lasttime) AS datachange_lasttime FROM o_policy_orderrelation GROUP BY orderid) AS b沪袭，然后原表a與臨時表b關(guān)聯(lián)查詢即可：WHERE a.orderid=b.orderid and a.datachange_lasttime=b.datachange_lasttime湾宙。

子查詢

如何才能對group by分組內(nèi)的數(shù)據(jù)進(jìn)行排序了，這個需要根據(jù)不同的需求處理冈绊。

1. 數(shù)據(jù)庫表設(shè)置了自增主鍵：主鍵id最大的侠鳄，更新時間肯定也是最新的。這種情況我們可以使用id代替時間去搜尋并組內(nèi)排序死宣，使用max(id)就可以獲取到每個分組中最大的記錄id（即最新的記錄）伟恶。

# 查詢orderid分組中最大id對應(yīng)的記錄
SELECT t2.max_id, t1.* FROM o_policy_orderrelation t1, (SELECT max(id) AS max_id FROM o_policy_orderrelation GROUP BY orderid) t2 WHERE t1.id = t2.max_id;

SELECT * FROM o_policy_orderrelation WHERE id IN (SELECT max(id) FROM o_policy_orderrelation GROUP BY orderid);

2. id與評論時間沒有關(guān)系，id大的評論時間可能不是最新：這種情況我們就需要使用max(datachange_lasttime)來獲取最新的記錄毅该，但因?yàn)椴煌唵蔚臅r間有可能相同博秫，因此還需要加多order_id這個條件去查詢潦牛。

# right join方式：使用right join可以減少外層的數(shù)據(jù)集。使用where orderid is not null可以使group by orderid時使用索引挡育。
# [mysql group by 組內(nèi)排序方法 - 傲雪星楓 - CSDN博客](https://blog.csdn.net/fdipzone/article/details/72453553)
SELECT a.* FROM o_policy_orderrelation AS a RIGHT JOIN 
(SELECT orderid, max(datachange_lasttime) AS max_time FROM o_policy_orderrelation WHERE orderid IS NOT NULL GROUP BY orderid) AS b 
ON a.orderid=b.orderid AND a.datachange_lasttime=b.max_time;

# GROUP BY HAVING方式
# [Group by ID having MAX(date) problem - Databases - The SitePoint Forums](https://www.sitepoint.com/community/t/group-by-id-having-max-date-problem/3765)
SELECT * FROM o_policy_orderrelation o
WHERE datachange_lasttime = (SELECT MAX(datachange_lasttime)
                              FROM o_policy_orderrelation
                              GROUP BY orderid
                              HAVING orderid = o.orderid);

# [mysql多表查詢及其 group by 組內(nèi)排序 - 北斗極星 - 博客園](https://www.cnblogs.com/hubing/p/4831836.html)
SELECT id, orderid, datachange_lasttime, eventpolicyid FROM o_policy_orderrelation AS t 
GROUP BY id, orderid, datachange_lasttime, eventpolicyid 
HAVING datachange_lasttime=(SELECT max(datachange_lasttime) FROM o_policy_orderrelation WHERE orderid=t.orderid);

# [這個需求能用一條 sql 完成嗎巴碗？ - V2EX](https://www.v2ex.com/t/382102)
SELECT * FROM o_policy_orderrelation t WHERE NOT EXISTS (SELECT * FROM o_policy_orderrelation WHERE orderid = t.orderid AND datachange_lasttime > t.datachange_lasttime);

JOIN + COUNT(*)

# [MySQL分組top N問題疑點(diǎn) - 知乎](https://zhuanlan.zhihu.com/p/66777046)
# 如果需要取TopN，只需要把<1改為<n
SELECT * FROM o_policy_orderrelation AS a
WHERE (
    SELECT count(*) FROM o_policy_orderrelation AS b WHERE a.orderid=b.orderid AND a.datachange_lasttime < b.datachange_lasttime
) < 1;

# [MySQL學(xué)習(xí)筆記：三種組內(nèi)排序方法 - Hider1214 - 博客園](https://www.cnblogs.com/hider/p/9087374.html)
# [【原】MySQL分組排序（包含組內(nèi)排名即寒、求中位數(shù)） - MERRU - 博客園](https://www.cnblogs.com/merru/articles/4626045.html)
# 如果還需要組內(nèi)排序顯示TopN的話橡淆，只用刪除HAVING子句就行了
SELECT a.id, a.orderid, a.datachange_lasttime, a.eventpolicyid, count(*) AS rank
FROM o_policy_orderrelation a
JOIN o_policy_orderrelation b ON a.orderid=b.orderid AND a.datachange_lasttime <= b.datachange_lasttime
GROUP BY a.orderid, a.datachange_lasttime HAVING rank=1
ORDER BY a.orderid, a.datachange_lasttime DESC;

# [MySQL實(shí)現(xiàn)over partition by（分組后對組內(nèi)數(shù)據(jù)排序） - MrCao杰羅爾德的博客 - CSDN博客](https://blog.csdn.net/m0_37797991/article/details/80511855)
SELECT *
FROM (SELECT
        t.orderid,
        t.datachange_lasttime,
        COUNT(*) AS rank
      FROM o_policy_orderrelation t
        LEFT JOIN o_policy_orderrelation r
          ON t.orderid = r.orderid
            AND t.datachange_lasttime = r.datachange_lasttime
      GROUP BY t.orderid) s
WHERE s.rank = 1;

SQL變量

SET @num := 0, @type := ''; 
SELECT eventid, 
       orderid, 
       policyid,
       datachange_lasttime 
FROM   (SELECT *, 
               @num := IF(@type = orderid, @num + 1, 1) AS row_number, 
               @type := orderid                         AS temp_type 
        FROM   o_policy_orderrelation 
        ORDER  BY orderid, 
                  datachange_lasttime DESC) AS a 
WHERE  a.row_number <= 1;

使用 group_concat() & substring_index() 函數(shù)

注意一點(diǎn)：group_concat的字段是int型時需要轉(zhuǎn)成字符型，否則可能出現(xiàn)結(jié)果不對的情況

select group_concat(orderid) id_list, name from ttt group by name; 分組取出所有orderid母赵，逗號分隔
select group_concat(orderid order by orderid SEPARATOR '_') from ttt group by name; 分組取出所有id逸爵，逗號分隔并排序，自定義分隔符"_"
select substring_index(group_concat(orderid order by orderid SEPARATOR '_'),'_',2) from ttt group by name; 分組取出所有id凹嘲，逗號分隔并排序取前兩個,自定義分隔符"_"

SELECT * FROM o_policy_orderrelation WHERE id IN(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY datachange_lasttime DESC),',',1) FROM o_policy_orderrelation GROUP BY orderid) ORDER BY datachange_lasttime DESC;

開窗函數(shù)

表 A 和表 B师倔，為 1 對多關(guān)系，如何查詢對于某個 A周蹭，多個 B 中最近更新的那一條記錄趋艘。
先把 B 表分組排序，row_number() over(partition by xx order by 時間) as 'rk',然后 select * from B 排序后的表凶朗，where rk =1致稀，再將其與 A 表關(guān)聯(lián)。

SQL 在分組查詢時俱尼，怎么獲取最新一條記錄 - V2EX

窗口分析函數(shù)

SELECT t.id,t.orderid,t.datachange_lasttime 
FROM(
SELECT id,orderid,datachange_lasttime,RANK() OVER (PARTITION BY orderid ORDER BY datachange_lasttime DESC) RK
FROM o_policy_orderrelation) t
WHERE t.RK<2

select t1.* from
(select (@rowNum1:=@rowNum1+1) as rowNo, id, orderid, datachange_lasttime, policyid from o_policy_orderrelation a, (Select (@rowNum1 :=0)) b order by a.orderid,a.datachange_lasttime desc) t1 left join
(select (@rowNum2:=@rowNum2+1) as rowNo, id, orderid, datachange_lasttime, policyid from o_policy_orderrelation c, (Select (@rowNum2 :=1)) d order by c.orderid,c.datachange_lasttime desc) t2 on t1.rowNo=t2.rowNO
where t1.orderid<>t2.orderid or t2.orderid is null

mysql下實(shí)現(xiàn)窗口分析函數(shù) - q195136130的專欄 - CSDN博客
MySQL實(shí)現(xiàn)over partition by（分組后對組內(nèi)數(shù)據(jù)排序） - MrCao杰羅爾德的博客 - CSDN博客

無效的錯誤查詢語句示例

組內(nèi)排序

先在派生表中排序，得出結(jié)果后再分組萎攒，從而實(shí)現(xiàn)了組內(nèi)排序遇八。但是在 5.7 中，首先需要關(guān)閉 sql_mode = ONLY_FULL_GROUP_BY耍休；相同的 name 值刃永，返回則是取了最早寫入的數(shù)據(jù)行，忽略了order by no desc羊精，按照數(shù)據(jù)的邏輯存儲順序來返回斯够。

# [踩坑雜談二：MYSQL分組組內(nèi)排序 - - ITeye博客](https://jdkleo.iteye.com/blog/2393834)
SELECT *
FROM (SELECT * FROM o_policy_orderrelation ORDER BY orderid, datachange_lasttime DESC) AS t
GROUP BY t.orderid ORDER BY datachange_lasttime DESC;

然而，5.7以后對排序的sql解析做了優(yōu)化喧锦，子查詢中的排序是會被忽略的读规。5.6這樣寫是沒問題的，5.7的話需要換一換了燃少，使用聚合函數(shù)取出你要的記錄再關(guān)聯(lián)原表獲取第一條記錄束亏，或者使用組內(nèi)排序生成行號后再按行號取第一條也行。

參考資料

• MySQL SQL GROUP BY是如何選擇哪一條數(shù)據(jù)留下的? - 知乎
• 在SQL中阵具，如何查詢某一字段中最大值的數(shù)據(jù)碍遍？ - 知乎
• 185 Department Top Three Salaries
• mysql中,你們是如何處理 order by 和 group by 的? - 知乎
• SQL在分組查詢時定铜，怎么獲取最新一條記錄？ - rebiekong的回答 - SegmentFault 思否
• group by分組后獲得每組中時間最大的那條記錄 - 積累與沉淀 - CSDN博客
• 最近消息列表sql語句
• sql - Select first row in each GROUP BY group? - Stack Overflow
• mysql經(jīng)驗(yàn)匯總
• MySQL 5.6 5.7 中組內(nèi)排序的區(qū)別 - Yifans_Z Blog
• MySQL 組內(nèi)排序 - 簡書
• 這個需求能用一條 sql 完成嗎怕敬？ - V2EX