GROUP BY` 是聚合分析的核心工具,但許多開發者僅停留在「單字段分組 + COUNT」層面。本文深入講解 HAVING 篩選機制、多字段分組語義、子查詢嵌套模式及執行順序陷阱。
SELECT
分組字段,
聚合函數(列) AS 別名
FROM 表名
[WHERE 條件]
GROUP BY 分組字段1, 分組字段2...
[HAVING 分組後條件]
[ORDER BY ...]
[LIMIT ...];
SELECT 列表限制:只能包含
GROUP BY 中的字段COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT 等)SELECT * FROM (
SELECT `category`, COUNT(*) AS total
FROM `products`
GROUP BY `category`
) AS t
WHERE total > 10 AND category <> '';
解析:內層完成分組聚合,外層對聚合結果 total 進行篩選。等價於直接用 HAVING:
SELECT `category`, COUNT(*) AS total
FROM `products`
GROUP BY `category`
HAVING COUNT(*) > 10 AND category <> '';
SELECT
student_id AS 學員編號,
course_id AS 內部測試,
AVG(score) AS 平均成績
FROM score
GROUP BY student_id, course_id;
分組鍵為 (student_id, course_id) 元組,每個唯一組合輸出一行。
SELECT
student_id, course_id, AVG(score) AS avg_score
FROM score
GROUP BY student_id, course_id
HAVING AVG(score) > 60;
WHERE vs HAVING 核心區別:
| 維度 | WHERE | HAVING |
|---|---|---|
| 作用階段 | 分組前(行級過濾) | 分組後(組級過濾) |
| 可用列 | 表列、衍生列 | 分組列、聚合函數 |
| 聚合函數 | 不可用 | 可用 |
| 索引利用 | 可用索引 | 不可用索引 |
SQL 邏輯執行順序(非書寫順序):
FROM / JOIN — 確定數據源WHERE — 行級過濾GROUP BY — 分組聚合函數 — 計算組級指標HAVING — 組級過濾SELECT — 投影輸出列ORDER BY — 排序LIMIT — 限制輸出行數-- 錯誤:WHERE 使用聚合函數
SELECT category, COUNT(*) FROM products WHERE COUNT(*) > 10 GROUP BY category;
-- ERROR 1111 (HY000): Invalid use of group function
-- 正確:用 HAVING
SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10;
-- 錯誤:SELECT 非分組非聚合列(ONLY_FULL_GROUP_BY 模式報錯)
SELECT id, category, COUNT(*) FROM products GROUP BY category;
-- ERROR 1055: 'id' isn't in GROUP BY
-- 正確:id 加入聚合或分組
SELECT MAX(id), category, COUNT(*) FROM products GROUP BY category;
-- 或
SELECT id, category, COUNT(*) FROM products GROUP BY id, category;
SELECT
category,
COUNT(*) AS cnt,
GROUP_CONCAT(name ORDER BY price DESC SEPARATOR ';') AS top_products
FROM products
GROUP BY category;
SELECT
COALESCE(category, '總計') AS category,
COUNT(*) AS cnt
FROM products
GROUP BY category WITH ROLLUP;
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
)
SELECT * FROM ranked WHERE rn <= 3;
INDEX idx_category (category)SELECT *,僅選取必要列SQL_BIG_RESULT 提示使用磁盤臨時表INDEX idx_cat_price (category, price) 滿足 GROUP BY category ORDER BY priceONLY_FULL_GROUP_BY 僅作兼容舊代碼過渡,新代碼嚴格遵循標準