MySQL HAVING 过滤分组

HAVING 子句用于对 GROUP BY 分组后的结果进行过滤，区别于 WHERE 过滤原始行。

基本语法

SELECT 列名, 聚合函数(列名)
FROM 表名
GROUP BY 列名
HAVING 条件;

HAVING 与 WHERE 的区别

-- 创建示例表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer VARCHAR(50),
    amount DECIMAL(10, 2),
    status VARCHAR(20)
);

INSERT INTO orders VALUES
(1, '张三', 100, 'paid'),
(2, '张三', 200, 'paid'),
(3, '李四', 150, 'paid'),
(4, '李四', 50, 'pending'),
(5, '王五', 300, 'paid');

-- WHERE: 过滤原始行（分组前）
SELECT customer, SUM(amount) AS 总金额
FROM orders
WHERE status = 'paid'
GROUP BY customer;

-- HAVING: 过滤分组（分组后）
SELECT customer, SUM(amount) AS 总金额
FROM orders
GROUP BY customer
HAVING SUM(amount) > 200;

使用聚合函数过滤

-- 筛选总金额超过200的客户
SELECT customer, SUM(amount) AS 总金额
FROM orders
GROUP BY customer
HAVING SUM(amount) > 200;

-- 筛选订单数大于2的客户
SELECT customer, COUNT(*) AS 订单数
FROM orders
GROUP BY customer
HAVING COUNT(*) > 2;

-- 使用别名
SELECT customer, SUM(amount) AS 总金额
FROM orders
GROUP BY customer
HAVING 总金额 > 200;

WHERE 和 HAVING 组合

-- 先过滤状态，再筛选高消费客户
SELECT customer, SUM(amount) AS 总金额
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) >= 200;

多条件过滤

-- 多个条件
SELECT customer, SUM(amount) AS 总金额, COUNT(*) AS 订单数
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100 AND COUNT(*) >= 2;

HAVING 支持的操作

-- 比较运算
HAVING COUNT(*) >= 3
HAVING AVG(amount) < 500

-- BETWEEN
HAVING SUM(amount) BETWEEN 100 AND 500

-- IN
HAVING customer IN ('张三', '李四')

-- LIKE
HAVING customer LIKE '张%'

执行顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

要点总结

• HAVING 用于过滤 GROUP BY 后的分组
• HAVING 可以使用聚合函数，WHERE 不可以
• WHERE 在 GROUP BY 之前，HAVING 在之后
• 两者可以组合使用