GROUP BY col1, col2 先按 col1 分組,組內再按 col2 細分。ONLY_FULL_GROUP_BY 嚴格模式)。YEAR(date)),GROUP BY 必須寫相同表達式,不能用別名。WHERE → GROUP BY → HAVING → ORDER BY → LIMIT。| 維度 | WHERE | HAVING |
|---|---|---|
| 作用對象 | 原始行 | 分組後的組 |
| 執行時機 | 分組前 | 分組後 |
| 可用聚合函數 | 否 | 是 |
| 典型用途 | 過濾數據源 | 過濾聚合結果 |
ORDER BY NULL 可提升性能(參見優化文章)。FROM / JOIN - 組裝數據源WHERE - 行級過濾GROUP BY - 分組HAVING - 組級過濾ORDER BY - 排序SELECT - 輸出投影LIMIT - 限制行數SELECT 考生姓名, MAX(總成績) AS max總成績
FROM tb_Grade
WHERE 考生姓名 IS NOT NULL
GROUP BY 考生姓名
HAVING MAX(總成績) > 600
ORDER BY max總成績;
表 comments(id, newsID, comment, theTime)。
SELECT tt.id, tt.newsID, tt.comment, tt.theTime
FROM (
SELECT id, newsID, comment, theTime
FROM comments
ORDER BY theTime DESC
) AS tt
GROUP BY tt.newsID;
SELECT c1.id, c1.newsID, c1.comment, c1.theTime
FROM comments c1
WHERE c1.theTime = (
SELECT MAX(c2.theTime)
FROM comments c2
WHERE c2.newsID = c1.newsID
);
配合 INDEX(newsID, theTime) 效能極佳。