HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
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;
演示数据库
在猿变手册教程中，我们将使用 yuanbian 样本数据库。
下面是选自 "goods" 表的数据：
+----------+-----------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------+-------------+
| 10 | 猿变手册之C++ | 35 |
| 11 | 猿变手册之Python | 25 |
| 12 | 猿变手册之疯狂前端 | 30 |
+----------+-----------------------------+-------------+下面是选自"orders" 表的数据：
+----------+--------------+----------+--------------+----------------+---------------------+
| order_id | order_no | goods_id | order_amount | order_quantity | create_date |
+----------+--------------+----------+--------------+----------------+---------------------+
| 1 | 201503120001 | 10 | 1000 | 10 | 2015-03-12 09:00:04 |
| 2 | 201503120002 | 12 | 1300 | 12 | 2015-03-12 17:23:10 |
| 3 | 201503130001 | 11 | 2400 | 22 | 2015-03-13 10:30:12 |
| 4 | 201503130002 | 12 | 2500 | 15 | 2015-03-13 11:00:05 |
| 5 | 201503130003 | 10 | 1400 | 15 | 2015-03-13 14:54:00 |
| 6 | 201503140001 | 12 | 2100 | 12 | 2015-03-14 11:34:00 |
+----------+--------------+----------+--------------+----------------+---------------------+
HAVING 示例
现在我们想要查找订单总金额大于 3000 的网站。
我们使用下面的 SQL 语句：
SELECT goods_name, SUM(orders.order_amount) AS total_amount FROM orders
INNER JOIN goods
ON goods.goods_id=orders.goods_id
GROUP BY orders.goods_id
HAVING total_amount > 3000;执行以上 SQL 输出结果如下：
+-----------------------------+--------------+
| goods_name | total_amount |
+-----------------------------+--------------+
| 猿变手册之疯狂前端 | 5900 |
+-----------------------------+--------------+
Having+WHERE示例
查找商品单价在30以上并且商品的订单总额在2000以上
SELECT goods_name, goods_price, SUM(orders.order_amount) AS total_amount FROM orders
INNER JOIN goods
ON goods.goods_id=orders.goods_id
WHERE goods.goods_price>=30
GROUP BY orders.goods_id
HAVING total_amount > 2000;执行以上 SQL 输出结果如下：
+-----------------------------+-------------+--------------+
| goods_name | goods_price | total_amount |
+-----------------------------+-------------+--------------+
| 猿变手册之C++ | 35 | 2400 |
| 猿变手册之疯狂前端 | 30 | 5900 |
+-----------------------------+-------------+--------------+
讨论区