在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
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; |
在本教程中,我们将使用 web3 样本数据库。
下面是选自 "Websites" 表的数据:
+----+--------------+---------------------------+-------+---------+ |
| id | name | url | alexa | country | |
+----+--------------+---------------------------+-------+---------+ |
| 1 | Google | https://www.google.cm/ | 1 | USA | |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | |
| 3 | 芝麻教程 | http://www.web3.xin/ | 4689 | CN | |
| 4 | 微博 | http://weibo.com/ | 20 | CN | |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA | |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | |
+----+---------------+---------------------------+-------+---------+ |
下面是 "access_log" 网站访问记录表的数据:
mysql> SELECT * FROM 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 | |
+-----+---------+-------+------------+ |
9 rows in set (0.00 sec) |
现在我们想要查找总访问量大于 200 的网站。
我们使用下面的 SQL 语句:
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; |
mysql> 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; |
+----------+---------------------------+------+ |
| name | url | nums | |
+----------+---------------------------+------+ |
| Facebook | https://www.facebook.com/ | 750 | |
| Google | https://www.google.cm/ | 275 | |
| 芝麻教程 | http://www.web3.xin/ | 521 | |
+----------+---------------------------+------+ |
3 rows in set (0.01 sec) |
现在我们想要查找总访问量大于 200 的网站,并且 alexa 排名小于 200。
我们在 SQL 语句中增加一个普通的 WHERE 子句:
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; |
mysql> select websites.name, websites.url, sum(access_log.count) 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; |
+----------+---------------------------+-----------------------+ |
| name | url | sum(access_log.count) | |
+----------+---------------------------+-----------------------+ |
| Facebook | https://www.facebook.com/ | 750 | |
| Google | https://www.google.cm/ | 275 | |
+----------+---------------------------+-----------------------+ |
2 rows in set (0.01 sec) |