很多開發者誤以為 GROUP BY tid ORDER BY dateline DESC 會先按時間倒序再分組,取每組最新記錄。實際上 MySQL 執行順序是:先分組(取每組第一行),再排序,導致得到的往往是每組最早而非最新記錄。
SELECT * FROM posts GROUP BY tid ORDER BY dateline DESC LIMIT 10;
結果:分組後再排序,組內仍是首行(最早記錄)。
SELECT * FROM posts GROUP BY tid DESC ORDER BY dateline DESC LIMIT 10;
結果:GROUP BY tid DESC 僅影響分組時的掃描順序,不改變組內行序,MySQL 文檔明確指出「GROUP BY 後跟 DESC/ASC 已廢棄且無效」。
SELECT * FROM posts GROUP BY tid, dateline DESC ORDER BY dateline DESC LIMIT 10;
實際等價於 GROUP BY tid, dateline,因時間戳幾乎唯一,導致每行成組,失去分組意義。
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 取每組第一行(即最新記錄)。
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+ 標準寫法
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY tid ORDER BY dateline DESC) AS rn
FROM posts
) t WHERE rn = 1;
表結構: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;
INDEX idx_tid_dateline (tid, dateline DESC) 支持索引下推WHERE id > last_max_id 游標分頁替代 OFFSETROW_NUMBER() 性能與可讀性雙贏MySQL GROUP BY 不具備排序功能。獲取「每組最新/最舊」記錄的標準模式是子查詢先排序再分組,或升級至 MySQL 8.0 使用窗口函數。切勿嘗試在 GROUP BY 字段後添加 DESC/ASC 或濫用 HAVING 過濾聚合值,均屬無效甚至錯誤寫法。