HAVING 子句 (SQL HAVING Clause)

HAVING 子句用於篩選 GROUP BY 分組後的結果。它的功能類似於 WHERE，但 WHERE 是在分組前篩選記錄，而 HAVING 是在分組後篩選群組。

為什麼需要 HAVING？

WHERE 子句無法與聚合函數一起使用。當你需要根據聚合函數的結果來篩選資料時，就必須使用 HAVING。

-- 錯誤：WHERE 無法搭配聚合函數
SELECT Customer, SUM(Price)
FROM orders
GROUP BY Customer
WHERE SUM(Price) > 1000;  -- ❌ 語法錯誤！

-- 正確：使用 HAVING
SELECT Customer, SUM(Price)
FROM orders
GROUP BY Customer
HAVING SUM(Price) > 1000;  -- ✅ 正確

HAVING 語法 (Syntax)

SELECT column1, aggregate_function(column2)
FROM table_name
[WHERE condition]
GROUP BY column1
HAVING aggregate_condition
[ORDER BY column];

執行順序：

1. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

HAVING 用法範例 (Example)

orders 資料表：

範例 1：篩選訂單總額大於 1000 的顧客

SELECT Customer, SUM(Price) AS TotalAmount
FROM orders
GROUP BY Customer
HAVING SUM(Price) > 1000;

結果：

李三的訂單總額為 500，不符合 HAVING 條件，被過濾掉了。

範例 2：篩選訂單數量大於 1 的顧客

SELECT Customer, COUNT(*) AS OrderCount
FROM orders
GROUP BY Customer
HAVING COUNT(*) > 1;

結果：

範例 3：WHERE 與 HAVING 同時使用

SELECT Customer, SUM(Price) AS TotalAmount
FROM orders
WHERE Price >= 500        -- 先篩選單筆金額 >= 500 的訂單
GROUP BY Customer
HAVING SUM(Price) > 2000; -- 再篩選總額 > 2000 的顧客

執行流程：

1. WHERE 先過濾掉單筆金額 < 500 的記錄
2. GROUP BY 對剩餘記錄分組
3. HAVING 篩選總額 > 2000 的群組

WHERE vs HAVING 比較

最佳實踐： 能用 WHERE 篩選的條件就用 WHERE，因為越早過濾資料，效能越好。

-- 較差效能：先分組再篩選
SELECT Customer, SUM(Price)
FROM orders
GROUP BY Customer
HAVING Customer = '張一';

-- 較好效能：先篩選再分組
SELECT Customer, SUM(Price)
FROM orders
WHERE Customer = '張一'
GROUP BY Customer;

HAVING 搭配多個條件

HAVING 可以使用 AND、OR 組合多個條件：

SELECT Customer, SUM(Price) AS TotalAmount, COUNT(*) AS OrderCount
FROM orders
GROUP BY Customer
HAVING SUM(Price) > 1000 AND COUNT(*) >= 2;

結果： 只返回訂單總額 > 1000 且訂單數量 >= 2 的顧客。

HAVING 搭配別名 (依資料庫而定)

某些資料庫允許在 HAVING 中使用 SELECT 中定義的別名：

-- MySQL 支援
SELECT Customer, SUM(Price) AS TotalAmount
FROM orders
GROUP BY Customer
HAVING TotalAmount > 1000;

-- 標準 SQL / 其他資料庫可能需要
SELECT Customer, SUM(Price) AS TotalAmount
FROM orders
GROUP BY Customer
HAVING SUM(Price) > 1000;

💡 建議： 為了跨資料庫相容性，在 HAVING 中重複使用聚合函數而非別名。

不搭配 GROUP BY 的 HAVING

技術上，HAVING 可以不搭配 GROUP BY 使用，此時整個結果集被視為一個群組：

-- 檢查是否有任何訂單總額超過 5000
SELECT SUM(Price) AS TotalSales
FROM orders
HAVING SUM(Price) > 5000;
-- 如果總額 <= 5000，則不返回任何結果

但這種用法較少見，通常會改用 WHERE 或子查詢。

實際應用範例

1. 找出高價值客戶

SELECT Customer, SUM(Price) AS TotalSpent
FROM orders
GROUP BY Customer
HAVING SUM(Price) > 10000
ORDER BY TotalSpent DESC;

2. 找出平均訂單金額較高的產品

SELECT Product, AVG(Price) AS AvgPrice
FROM order_details
GROUP BY Product
HAVING AVG(Price) > 500;

3. 找出經常購買的客戶

SELECT Customer, COUNT(*) AS PurchaseCount
FROM orders
WHERE OrderDate >= '2024-01-01'
GROUP BY Customer
HAVING COUNT(*) >= 5;

相關主題

• GROUP BY - 資料分組
• WHERE - 篩選記錄
• 聚合函數 - COUNT, SUM, AVG, MAX, MIN