SQL HAVING 子句：分組後篩選條件

HAVING 子句用途

WHERE 無法搭配彙總函數使用，HAVING 專門用於對 GROUP BY 分組後的統計結果進行篩選。

語法結構

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

範例資料

Websites 表

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶         | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程     | https://www.runoob.com/    | 4689  | CN      |
| 4  | 微博         | https://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow| https://stackoverflow.com/ | 0     | IND     |
+----+--------------+---------------------------+-------+---------+

access_log 存取記錄表

+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
| 1   | 1       | 45    | 2016-05-10 |
| 2   | 3       | 100   | 2016-05-13 |
| 3   | 1       | 230   | 2016-05-14 |
| 4   | 2       | 10    | 2016-05-14 |
| 5   | 5       | 205   | 2016-05-14 |
| 6   | 4       | 13    | 2016-05-15 |
| 7   | 3       | 220   | 2016-05-15 |
| 8   | 5       | 545   | 2016-05-16 |
| 9   | 3       | 201   | 2016-05-17 |
+-----+---------+-------+------------+

實作範例

1. 總瀏覽量 > 200 的網站

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums
FROM access_log
INNER JOIN Websites ON access_log.site_id = Websites.id
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

2. 總瀏覽量 > 200 且 alexa 排名 < 200

SELECT Websites.name, SUM(access_log.count) AS nums
FROM Websites
INNER JOIN access_log ON Websites.id = access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;