详解SQL中不能轻视的 HAVING 子句

HAVING 子句是 SQL 中经常被初学者轻视但功能强大的子句，它专门用于对 GROUP BY 分组后的结果进行条件过滤。很多人容易把它和 WHERE 混淆，但它们有本质区别。

核心区别：HAVING vs WHERE

实际应用场景

1. 筛选聚合结果

-- 找出平均成绩大于85分的班级
SELECT class_id, AVG(score) as avg_score
FROM students
GROUP BY class_id
HAVING AVG(score) > 85;

-- 找出订单总数超过100的客户
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 100;

2. 多个聚合条件组合

-- 找出总销售额超过10000且平均订单额大于500的销售员
SELECT salesperson_id, 
       SUM(amount) as total_sales,
       AVG(amount) as avg_order
FROM sales
GROUP BY salesperson_id
HAVING SUM(amount) > 10000 
   AND AVG(amount) > 500;

3. 与 WHERE 配合使用

-- 先过滤无效数据，再分组统计
SELECT department_id, 
       COUNT(*) as employee_count,
       AVG(salary) as avg_salary
FROM employees
WHERE status = 'active'  -- 先过滤活跃员工
GROUP BY department_id
HAVING COUNT(*) >= 10     -- 再筛选员工数>=10的部门
   AND AVG(salary) > 50000;

4. 复杂条件判断

-- 使用CASE语句在HAVING中
SELECT product_category,
       SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as completed_sales
FROM orders
GROUP BY product_category
HAVING SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) > 10000;

常见误区与最佳实践

❌ 常见错误

-- 错误：在WHERE中使用聚合函数
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000  -- 错误！
GROUP BY department_id;

-- 正确：使用HAVING
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

✅ 性能优化建议

尽量在WHERE中过滤：先减少数据集大小

-- 优化前
SELECT user_id, COUNT(*)
FROM logs
GROUP BY user_id
HAVING COUNT(*) > 1000;

-- 优化后（如果数据库支持）
SELECT user_id, COUNT(*)
FROM logs
WHERE log_date >= '2024-01-01'  -- 先按时间过滤
GROUP BY user_id
HAVING COUNT(*) > 1000;

避免在HAVING中重复计算

-- 不推荐
SELECT department_id, 
       AVG(salary) as avg_sal,
       COUNT(*) as emp_count
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000 
   AND COUNT(*) > 5 
   AND AVG(salary)/COUNT(*) > 1000;  -- 重复计算

-- 推荐：使用子查询或CTE
WITH dept_stats AS (
  SELECT department_id,
         AVG(salary) as avg_sal,
         COUNT(*) as emp_count
  FROM employees
  GROUP BY department_id
)
SELECT *
FROM dept_stats
WHERE avg_sal > 50000 
  AND emp_count > 5
  AND avg_sal/emp_count > 1000;

进阶用法

1. HAVING 实现去重检查

-- 找出有重复邮箱的用户
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

2. 多级聚合

-- 找出每月总销售额都超过10000的年份
SELECT EXTRACT(YEAR FROM order_date) as year,
       COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) as months_active
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 12
   AND MIN(monthly_total) > 10000;

3. 与窗口函数结合

-- 找出销售额高于部门平均值的员工
WITH employee_sales AS (
  SELECT employee_id,
         department_id,
         SUM(amount) as total_sales,
         AVG(SUM(amount)) OVER (PARTITION BY department_id) as dept_avg
  FROM sales
  GROUP BY employee_id, department_id
)
SELECT *
FROM employee_sales
WHERE total_sales > dept_avg;

总结

HAVING 子句的关键要点：

掌握 HAVING 子句能让你写出更高效、更精确的分组查询，是 SQL 数据分析中不可或缺的工具。