SQL Server HAVING 使用示範

在 SQL Server 中，GROUP BY 關鍵字是用來將資料依據特定條件群組，我們可以使用 WHERE 條件過濾分組前的資料，而 HAVING 的用途則是過濾分組後的資料。

這裡有一個示範用的 Employee 資料表儲存員工資訊， DepartmentId 欄位則是儲存部門 Id：

    
+--+-----+------------+
|Id|Name |DepartmentId|
+--+-----+------------+
|1 |美美  |1          |
|2 |大頭  |1          |
|3 |小明  |2          |
|4 |阿花  |null       |
|5 |小王  |2          |
+--+----+------------+
    

如果要查詢每個部門的人數可以使用 GROUP BY：

    
SELECT DepartmentId, COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY DepartmentId
    

查詢結果：
(null 代表沒有部門)

    
+------------+-------------+
|DepartmentId|EmployeeCount|
+------------+-------------+
|null        |1            |
|1           |2            |
|2           |2            |
+------------+-------------+
    

假設我們只要取得部門人數大於 1 的可以在外面多包一層查詢然後過濾：

    
SELECT *
FROM (SELECT DepartmentId, COUNT(*) AS EmployeeCount
      FROM Employee
      GROUP BY DepartmentId) AS t
WHERE t.EmployeeCount > 1
    

查詢結果：

    
+------------+-------------+
|DepartmentId|EmployeeCount|
+------------+-------------+
|1           |2            |
|2           |2            |
+------------+-------------+
    

但是使用 HAVING 就可以省去這一層，可以直接對群組的結果進行篩選：

    
SELECT DepartmentId, COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY DepartmentId
HAVING COUNT(*) > 1
    

參考資料：
Microsoft.Learn - SELECT - HAVING (Transact-SQL)