CoderPath

HAVING：分组后过滤 ​

WHERE vs HAVING ​

两者都用于过滤数据，但作用时机不同：

  • WHERE：在分组之前过滤行（基于原始数据）
  • HAVING：在分组之后过滤组（基于聚合结果）

示例对比：

sql
-- WHERE：只统计 Electronics 订单
SELECT 
    customer_name,
    COUNT(*) AS orders
FROM orders
WHERE product_category = 'Electronics'
GROUP BY customer_name;

结果（过滤后再分组）：

text
 customer_name | orders
---------------+--------
 Alice         |      2
 Bob           |      1
 Charlie       |      1
sql
-- HAVING：只显示订单数 > 1 的客户
SELECT 
    customer_name,
    COUNT(*) AS orders
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 1;

结果（分组后再过滤）：

text
 customer_name | orders
---------------+--------
 Alice         |      3
 Bob           |      2
 Charlie       |      2

HAVING 基础语法 ​

sql
SELECT column, aggregate_function(column)
FROM table
GROUP BY column
HAVING aggregate_condition;

HAVING 条件通常包含聚合函数。

HAVING 的使用场景 ​

场景 1：过滤聚合结果 ​

sql
-- 查询总消费超过 500 的客户
SELECT 
    customer_name,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_name
HAVING SUM(amount) > 500;

结果：

text
 customer_name | total_spent
---------------+-------------
 Alice         |     1525.00

场景 2：过滤计数结果 ​

sql
-- 查询订单数量 >= 2 的客户
SELECT 
    customer_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name
HAVING COUNT(*) >= 2
ORDER BY order_count DESC;

结果：

text
 customer_name | order_count
---------------+-------------
 Alice         |           3
 Bob           |           2
 Charlie       |           2

场景 3：过滤平均值 ​

sql
-- 查询平均订单金额超过 200 的客户
SELECT 
    customer_name,
    COUNT(*) AS orders,
    ROUND(AVG(amount), 2) AS average_order
FROM orders
GROUP BY customer_name
HAVING AVG(amount) > 200;

结果：

text
 customer_name | orders | average_order
---------------+--------+---------------
 Alice         |      3 |        508.33

WHERE 和 HAVING 结合使用 ​

可以同时使用 WHERE 和 HAVING：

sql
-- WHERE：先过滤 2026-01-17 之后的订单
-- HAVING：再过滤订单数 >= 2 的客户
SELECT 
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-17'
GROUP BY customer_name
HAVING COUNT(*) >= 2;

执行顺序：

  1. FROM：获取 orders 表
  2. WHERE：过滤日期
  3. GROUP BY：按客户分组
  4. HAVING：过滤组
  5. SELECT：选择列
  6. ORDER BY：排序（如果有）

HAVING 中的条件 ​

可以使用的条件 ​

HAVING 可以包含：

  1. 聚合函数（最常见）
sql
HAVING COUNT(*) > 2
HAVING SUM(amount) > 1000
HAVING AVG(amount) >= 100
  1. 分组列（较少见，但合法）
sql
SELECT 
    product_category,
    COUNT(*) AS orders
FROM orders
GROUP BY product_category
HAVING product_category IN ('Electronics', 'Furniture');

注意：上面的查询用 WHERE 更高效：

sql
-- 更好的写法
SELECT 
    product_category,
    COUNT(*) AS orders
FROM orders
WHERE product_category IN ('Electronics', 'Furniture')
GROUP BY product_category;
  1. 组合条件
sql
SELECT 
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY customer_name
HAVING COUNT(*) >= 2 AND SUM(amount) > 300;

不能使用的条件 ​

HAVING 不能直接引用 SELECT 中的列别名（部分数据库例外）：

sql
-- 错误（PostgreSQL）：order_count 还未计算
SELECT 
    customer_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name
HAVING order_count > 2;  -- 错误！

正确写法：

sql
SELECT 
    customer_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 2;  -- 正确

MySQL 例外：MySQL 允许在 HAVING 中使用列别名：

sql
-- MySQL 支持
SELECT 
    customer_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name
HAVING order_count > 2;

但为了跨数据库兼容，建议使用原始聚合函数表达式。

HAVING 与子查询对比 ​

某些 HAVING 查询可以用子查询替代：

使用 HAVING：

sql
SELECT 
    customer_name,
    SUM(amount) AS total
FROM orders
GROUP BY customer_name
HAVING SUM(amount) > (SELECT AVG(total_per_customer) 
                      FROM (SELECT SUM(amount) AS total_per_customer 
                            FROM orders 
                            GROUP BY customer_name) AS t);

虽然复杂，但 HAVING 通常更简洁和高效。

实际应用示例 ​

示例 1：找出高价值客户 ​

sql
-- 定义：总消费 > 1000 或订单数 >= 3
SELECT 
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_name
HAVING SUM(amount) > 1000 OR COUNT(*) >= 3
ORDER BY total_spent DESC;

示例 2：找出异常订单模式 ​

sql
-- 平均订单金额特别高的客户（可能是企业客户）
SELECT 
    customer_name,
    COUNT(*) AS orders,
    ROUND(AVG(amount), 2) AS avg_order
FROM orders
GROUP BY customer_name
HAVING AVG(amount) > 300
ORDER BY avg_order DESC;

示例 3：分类汇总与过滤 ​

sql
-- 每个类别中，订单数 >= 2 的客户
SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY product_category, customer_name
HAVING COUNT(*) >= 2
ORDER BY product_category, total DESC;

WHERE vs HAVING 选择指南 ​

条件类型使用 WHERE使用 HAVING
基于原始列✅❌（用 WHERE 更高效）
基于聚合结果❌（不允许）✅
执行时机分组前分组后
性能更快（减少分组数据）较慢（先分组再过滤）

规则：

  • 能用 WHERE 就用 WHERE（更高效）
  • 只有在需要过滤聚合结果时才用 HAVING

常见错误 ​

错误 1：在 WHERE 中使用聚合函数 ​

sql
-- 错误
SELECT customer_name, SUM(amount) 
FROM orders 
WHERE SUM(amount) > 1000  -- 错误！
GROUP BY customer_name;

正确写法：

sql
SELECT customer_name, SUM(amount) 
FROM orders 
GROUP BY customer_name 
HAVING SUM(amount) > 1000;

错误 2：HAVING 用于非聚合条件 ​

sql
-- 低效
SELECT customer_name, COUNT(*) 
FROM orders 
GROUP BY customer_name 
HAVING customer_name = 'Alice';

更好的写法（用 WHERE）：

sql
SELECT customer_name, COUNT(*) 
FROM orders 
WHERE customer_name = 'Alice'
GROUP BY customer_name;

错误 3：忘记 GROUP BY ​

sql
-- 错误：HAVING 需要 GROUP BY
SELECT customer_name, SUM(amount) 
FROM orders 
HAVING SUM(amount) > 1000;

练习 ​

使用 orders 表完成以下练习：

  1. 基本 HAVING：查询订单数量超过 2 的客户。

  2. 金额过滤：查询总消费金额超过 200 的客户，显示客户名称和总金额。

  3. 平均值过滤：查询平均订单金额超过 150 的客户。

  4. WHERE + HAVING：查询 Electronics 类别中，订单数量 >= 2 的客户。

  5. 多条件 HAVING：查询订单数量 >= 2 且总金额 >= 300 的客户。

  6. 分类统计：查询每个产品类别中订单数量最多的前 2 名客户（提示：子查询或窗口函数）。

  7. 对比查询：分别用 WHERE 和 HAVING 实现"查询 Alice 的订单统计"，观察两者的差异。

  8. 实际场景：查询每个类别中，平均订单金额超过该类别所有订单平均金额的客户（提示：需要子查询）。

完成练习后，打开本章答案页核对。

下一步 ​

学会了 HAVING 过滤分组。下一节学习多列分组和 ROLLUP：如何生成小计和总计。

回到第二章概览 · 返回SQL 学习路线

用心记录每一步成长

Copyright © 2026-present Twiink