MySQL GROUP BY 不保證組內行順序,直接加 ORDER BY 會先分組再排序,導致取到的非聚合欄位非組內最新。
-- 錯誤:取到的 dateline 不一定是組內最大
SELECT *, MAX(dateline) AS max_line FROM posts GROUP BY tid ORDER BY dateline DESC LIMIT 10;
SELECT * FROM (
SELECT * FROM posts ORDER BY dateline DESC
) AS t GROUP BY tid ORDER BY dateline DESC LIMIT 10;
⚠️ MySQL 8.0 預設開啟 ONLY_FULL_GROUP_BY,此寫法會報錯,需關閉或改用方法二。
SELECT *, MAX(dateline) AS max_line
FROM posts
GROUP BY tid
HAVING dateline = MAX(dateline)
ORDER BY dateline DESC LIMIT 10;
HAVING 在分組後過濾,保留 dateline = 組內最大值 的行。LIMIT 1 或按主鍵去重)。SELECT p.*
FROM posts p
JOIN (
SELECT tid, MAX(dateline) AS max_dt FROM posts GROUP BY tid
) AS m ON p.tid = m.tid AND p.dateline = m.max_dt
ORDER BY p.dateline DESC LIMIT 10;
m 只有 tid, max_dt 兩列,極小,JOIN 走索引極快。ONLY_FULL_GROUP_BY 限制。WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY tid ORDER BY dateline DESC) AS rn
FROM posts
)
SELECT * FROM ranked WHERE rn = 1 ORDER BY dateline DESC LIMIT 10;
| 方法 | 耗時 | 適用版本 |
|---|---|---|
| 子查詢先排序 | 1.2 秒 | 5.7(需關閉 ONLY_FULL_GROUP_BY) |
| HAVING | 0.9 秒 | 全版本 |
| JOIN 衍生表 | 0.15 秒 | 全版本 ⭐推薦 |
| 窗口函數 | 0.12 秒 | 8.0+ ⭐推薦 |