HAVING 筛选分组

第 9 课，共 21 课 · 第 2 部分 · 汇总 · 约 15 分钟

上一课按客户分组，数出了每个客户的订单数。现在只想要下过 2 单以上的客户。先看看全部：

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4ORDER BY customer_id;

输出

customer_id	order_count
1	3
2	2
3	1
4	2
5	2
7	1
8	1

7 行

要的是 order_count 至少是 2 的那几行。这是对分组提的条件，要用 HAVING。

HAVING

HAVING 写在 GROUP BY 后面，条件里可以用聚合函数：

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4HAVING COUNT(*) >= 25ORDER BY customer_id;

输出

customer_id	order_count
1	3
2	2
4	2
5	2

4 行

点单步看看：GROUP BY 先把订单分成 7 组，HAVING 再一组一组看，COUNT(*) 不到 2 的组整组删掉，剩下 4 组。

HAVING 里什么聚合函数都可以用，也不一定要出现在 SELECT 里。金额超过 40 的订单：

main.sql

1SELECT order_id2FROM order_items3GROUP BY order_id4HAVING SUM(quantity * unit_price) > 405ORDER BY order_id;

输出

order_id
3
5
7
8

4 行

WHERE 里不能放聚合函数

为什么不直接写 WHERE COUNT(*) >= 2？试试：

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3WHERE COUNT(*) >= 24GROUP BY customer_id;

输出

ERROR 1111 (HY000): Invalid use of group function

聚合函数（COUNT、SUM、AVG…）不能写在 WHERE 里：WHERE 是一行一行看的，还没分组。对分组以后的结果筛选，用 HAVING。

ERROR 1111：聚合函数用错了地方。WHERE 是在分组之前一行一行看的，那时候还没有组，COUNT(*) 数不出来。

WHERE 和 HAVING 的区别

两个都是「留下符合条件的」，区别在什么时候、对谁：

	什么时候	看的是	能用聚合函数吗
`WHERE`	分组之前	每一行	不能
`HAVING`	分组之后	每一组	能

两个可以一起用。只算已发货的订单，找出有 2 单已发货的客户：

main.sql

1SELECT customer_id, COUNT(*) AS shipped2FROM orders3WHERE status = '已发货'4GROUP BY customer_id5HAVING COUNT(*) >= 26ORDER BY customer_id;

输出

customer_id	shipped
1	2
5	2

2 行

再点单步，这次看得更清楚：WHERE 先把待发货和已取消的订单一行一行删掉，剩下的才去分组；分完组，HAVING 再删掉不到 2 单的组。2 号客户下过 2 单，可其中一单待发货，到 HAVING 时他的组只剩 1 行，于是不见了。

先猜一猜

价格超过 10 的商品：双肩包、帆布袋（箱包），水杯（厨房），耳机、台灯、手机壳（数码）。结果有几行？

1SELECT category, COUNT(*)2FROM products3WHERE price > 104GROUP BY category5HAVING COUNT(*) >= 2;

WHERE 先删掉 10 块以下的商品，文具一件不剩，连组都没有。剩下 3 组：箱包 2 件、厨房 1 件、数码 3 件；HAVING 删掉厨房，结果 2 行。

能用 WHERE 就用 WHERE

条件和聚合无关的，WHERE 和 HAVING 都能写，结果一样：

main.sql

1SELECT category, COUNT(*)2FROM products3GROUP BY category4HAVING category <> '数码'5ORDER BY category;

输出

category	COUNT(*)
厨房	2
文具	2
箱包	2

3 行

可这样是先把数码类的 4 行也分了组、算完了，最后才扔掉。写成 WHERE category <> '数码'，一开始就不要它们，更直接。规矩很简单：**对行的条件写 WHERE，对聚合结果的条件写 HAVING**。

平均价高的分类

平均价格超过 20 的分类：

main.sql

1SELECT category, ROUND(AVG(price), 2) AS avg_price2FROM products3GROUP BY category4HAVING AVG(price) > 205ORDER BY category;

输出

category	avg_price
数码	26.88
箱包	25.45

2 行

小技巧： MySQL 也允许在 HAVING 里用 SELECT 的别名（HAVING avg_price > 20），可别的数据库很多不行。把聚合函数再写一遍，到哪儿都能用。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。

练习 1

库存多的分类

查出库存加起来超过 100 的分类：两列 category 和 total_stock，按 category 排序。

要显示

category	total_stock
数码	245
文具	200

2 行

exercise1.sql

1SELECT category, SUM(stock) AS total_stock2FROM products3GROUP BY category4ORDER BY category;

输出

点「运行」或按 Ctrl+Enter

厨房正好 100，「超过 100」不包括它。

练习 2

常来的客户

不算已取消的订单，找出至少有 2 个订单的客户：两列 customer_id 和 order_count，按 customer_id 排序。

要显示

customer_id	order_count
1	3
2	2
5	2

3 行

exercise2.sql

1SELECT customer_id, status FROM orders;

输出

点「运行」或按 Ctrl+Enter

练习 3

修好它：ERROR 1111

想找出一共买了 4 件以上（含 4 件）的订单，却报了 ERROR 1111。改好它，列是 order_id 和 items，按 order_id 排序。

要显示

order_id	items
1	4
5	4
6	5
9	4
12	4

5 行

exercise3.sql

1SELECT order_id, SUM(quantity) AS items2FROM order_items3WHERE SUM(quantity) >= 44GROUP BY order_id5ORDER BY order_id;

输出

点「运行」或按 Ctrl+Enter

这一课学会了

HAVING 写在 GROUP BY 后面，筛选的是分组，条件里可以用聚合函数。
WHERE 在分组之前删行，HAVING 在分组之后删组。
WHERE 里放聚合函数会得到 ERROR 1111。
两个可以一起用：对行的条件写 WHERE，对聚合结果的条件写 HAVING。
HAVING 里用 SELECT 的别名只有部分数据库可以，把聚合函数再写一遍最稳。