萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL GROUP BY 與 ORDER BY 協同排序陷阱:子查詢法獲取分組最新記錄

核心問題:GROUP BY 不保證組內排序

很多開發者誤以為 GROUP BY tid ORDER BY dateline DESC 會先按時間倒序再分組,取每組最新記錄。實際上 MySQL 執行順序是:先分組(取每組第一行),再排序,導致得到的往往是每組最早而非最新記錄。

錯誤嘗試與誤區分析

❌ 錯誤 1:直接加 ORDER BY

SELECT * FROM posts GROUP BY tid ORDER BY dateline DESC LIMIT 10;

結果:分組後再排序,組內仍是首行(最早記錄)。

❌ 錯誤 2:GROUP BY 字段後加 DESC

SELECT * FROM posts GROUP BY tid DESC ORDER BY dateline DESC LIMIT 10;

結果:GROUP BY tid DESC 僅影響分組時的掃描順序,不改變組內行序,MySQL 文檔明確指出「GROUP BY 後跟 DESC/ASC 已廢棄且無效」。

❌ 錯誤 3:多字段分組偽裝排序

SELECT * FROM posts GROUP BY tid, dateline DESC ORDER BY dateline DESC LIMIT 10;

實際等價於 GROUP BY tid, dateline,因時間戳幾乎唯一,導致每行成組,失去分組意義。

❌ 錯誤 4:MAX 聚合函數配合 HAVING

SELECT *, MAX(dateline) AS max_dt
FROM posts GROUP BY tid
HAVING dateline = MAX(dateline) ORDER BY dateline DESC LIMIT 10;

結果:HAVING 在分組後過濾,只有組內單行記錄才能滿足 dateline = MAX(dateline),多行組全部被過濾掉。

正確解法:子查詢先排序再分組

✅ 方案一:內層排序 + 外層分組(最通用)

SELECT *
FROM (
    SELECT * FROM posts ORDER BY dateline DESC
) AS t
GROUP BY tid
ORDER BY dateline DESC
LIMIT 10;

原理:內層先按時間倒序物理排列,外層 GROUP BY 取每組第一行(即最新記錄)。

✅ 方案二:GROUP_CONCAT + 子查詢(適用於只需主鍵)

SELECT * FROM `test`
WHERE id IN (
    SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY `date` DESC), ',', 1)
    FROM `test` GROUP BY category_id
)
ORDER BY `date` DESC;

注意:GROUP_CONCAT 默認長度限制 1024 字節,大組需調大 group_concat_max_len。

✅ 方案三:關聯子查詢(MySQL 8.0+ 推薦窗口函數)

-- MySQL 8.0+ 標準寫法
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY tid ORDER BY dateline DESC) AS rn
    FROM posts
) t WHERE rn = 1;

實戰案例:Discuz 論壇「最新回覆」查詢

表結構:posts(id, tid, subject, message, dateline)
需求:查前 10 個不同主題的最新回覆。

SELECT *
FROM (
    SELECT * FROM posts ORDER BY dateline DESC
) AS p
GROUP BY tid
ORDER BY dateline DESC
LIMIT 10;

性能優化建議

  1. 覆蓋索引:INDEX idx_tid_dateline (tid, dateline DESC) 支持索引下推
  2. 避免 SELECT *:只查業務必要列
  3. 大表分頁:配合 WHERE id > last_max_id 游標分頁替代 OFFSET
  4. MySQL 8.0 升級:原生窗口函數 ROW_NUMBER() 性能與可讀性雙贏

總結

MySQL GROUP BY 不具備排序功能。獲取「每組最新/最舊」記錄的標準模式是子查詢先排序再分組,或升級至 MySQL 8.0 使用窗口函數。切勿嘗試在 GROUP BY 字段後添加 DESC/ASC 或濫用 HAVING 過濾聚合值,均屬無效甚至錯誤寫法。

copyright © 萬盛學電腦網 all rights reserved