SQL HAVING 子句深度解析：分組後的篩選利器

HAVING vs WHERE：核心區別

語法結構

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING aggregate_condition;

實戰案例：部門平均薪資篩選

-- 1. 查詢各部門平均薪資
SELECT dept, AVG(salary) AS avg_sal
FROM employee
GROUP BY dept;

-- 2. 僅保留平均薪資 > 20000 的部門
SELECT dept, AVG(salary) AS avg_sal
FROM employee
GROUP BY dept
HAVING AVG(salary) > 20000;

第 2 步若改用 WHERE AVG(salary) > 20000 會報錯，因為聚合函數不可用於 WHERE。

進階用法

• 多條件組合：HAVING COUNT(*) > 5 AND AVG(salary) > 50000
• 搭配窗口函數：現代 SQL 可用 QUALIFY（部分資料庫支援）替代 HAVING
• 性能提示：先用 WHERE 過濾掉不需要的行，再 GROUP BY，最後 HAVING，減少分組運算量