MySQL / 系统课时
GROUP BY 与 HAVING
GROUP BY 与 HAVING
当统计结果需要按客户、部门或状态分别计算时,单个聚合值已经不够。本课通过订单数据掌握 GROUP BY 的分组规则,以及为什么聚合后的筛选必须使用 HAVING 而不是 WHERE。
学习目标
- 使用
GROUP BY按指定列生成多组聚合结果 - 区分
WHERE与HAVING的执行对象和使用位置 - 能组合分组、聚合与排序完成常见统计查询
准备分组数据
先创建练习数据,其中同一个客户可以拥有多笔订单。
CREATE TEMPORARY TABLE demo_orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO demo_orders (id, customer_id, amount) VALUES
(1, 101, 120.00),
(2, 102, 80.00),
(3, 101, 200.00),
(4, 103, 60.00),
(5, 102, 150.00);预期结果
Query OK, 5 rows affected使用 GROUP BY 分组聚合
GROUP BY 会按照指定列的值把记录划分为多个组,聚合函数随后在每一组内部独立计算。下面统计每个客户的订单数与消费总额。
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM demo_orders
GROUP BY customer_id
ORDER BY customer_id;预期结果
+-------------+-------------+--------------+
| customer_id | order_count | total_amount |
+-------------+-------------+--------------+
| 101 | 2 | 320.00 |
| 102 | 2 | 230.00 |
| 103 | 1 | 60.00 |
+-------------+-------------+--------------+没有 GROUP BY 时,全部记录属于一个统计范围;加入 GROUP BY customer_id 后,每个不同的 customer_id 都形成独立分组,因此查询返回多行统计结果。
使用 HAVING 筛选聚合结果
如果只保留消费总额至少为 200 的客户,需要等 SUM(amount) 计算完成后再筛选,因此应使用 HAVING。
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM demo_orders
GROUP BY customer_id
HAVING SUM(amount) >= 200
ORDER BY total_amount DESC;预期结果
+-------------+-------------+--------------+
| customer_id | order_count | total_amount |
+-------------+-------------+--------------+
| 101 | 2 | 320.00 |
| 102 | 2 | 230.00 |
+-------------+-------------+--------------+HAVING 面向已经形成的分组及其聚合结果,所以能够判断 SUM(amount)、COUNT(*) 等统计值。
区分 WHERE 与 HAVING
WHERE 筛选原始行,发生在分组之前;HAVING 筛选分组结果,发生在聚合之后。两者可以同时出现,各自完成不同阶段的过滤。
例如先排除金额低于 100 的订单,再只保留过滤后至少有两笔订单的客户:
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM demo_orders
WHERE amount >= 100
GROUP BY customer_id
HAVING COUNT(*) >= 2;预期结果
+-------------+-------------+--------------+
| customer_id | order_count | total_amount |
+-------------+-------------+--------------+
| 101 | 2 | 320.00 |
+-------------+-------------+--------------+客户 102 原本有两笔订单,但金额为 80 的记录先被 WHERE 排除,因此分组后只剩一笔,不满足 HAVING COUNT(*) >= 2。
避免选择未分组的普通列
分组查询中的非聚合列应与分组键保持一致。例如按 customer_id 分组时,可以直接选择 customer_id,但不要随意同时选择没有参与分组、又没有使用聚合函数处理的其他普通列,否则结果可能不符合预期,并可能在启用 ONLY_FULL_GROUP_BY 时直接报错。
处理分组统计时,可以按“先确定原始行过滤条件,再确定分组键,再选择聚合指标,最后决定是否用 HAVING 筛选分组”的顺序组织查询。这样能清楚区分每个条件到底作用于原始记录还是统计结果。