7.3 分组筛选：HAVING子句

      在上一节中，我们学习了如何使用 GROUP BY 对数据进行分组，并结合聚合函数计算每个组的统计值。但很多时候，我们并不需要所有的分组结果——比如只想要平均分大于80的科目、或者总销售额超过10000的部门。这时就需要使用 HAVING 子句对分组后的结果进行筛选。

一、HAVING的核心作用：筛选分组

HAVING 子句专门用于对 GROUP BY 产生的分组进行过滤。它允许你在分组之后，根据聚合函数的计算结果（如 AVG(), SUM(), COUNT() 等）来决定保留哪些组。

为什么需要 HAVING？

• 无法在 WHERE 中使用聚合函数：因为 WHERE 是在分组前对原始行进行过滤，此时聚合函数的值还未计算出来。

• 精细化分析：只关注满足某些统计条件的分组，例如“平均分高于80的班级”、“订单数超过10的客户”。

二、HAVING 基础语法与位置

HAVING 子句必须跟在 GROUP BY 之后，ORDER BY 之前：

SELECT 分组列1, 分组列2, ..., 聚合函数(列)
FROM 表名
[WHERE 条件]                -- 分组前筛选行
GROUP BY 分组列1, 分组列2, ...
HAVING 分组筛选条件           -- 分组后筛选组
[ORDER BY 排序];

执行顺序：

1. FROM：定位表

2. WHERE：过滤行（不能使用聚合函数）

3. GROUP BY：分组

4. 聚合计算：对各组执行聚合函数

5. HAVING：根据聚合结果过滤组

6. SELECT：选择输出列

7. ORDER BY：排序

三、HAVING vs WHERE：关键区别

为了清晰理解两者的分工，请看下表：

示例对比

假设我们要找出平均分大于85的科目：

• 先按科目分组，计算平均分，然后保留平均分>85的组 → 必须用 HAVING。

• 如果我们想排除掉缺考的学生（成绩为NULL）后再分组，则用 WHERE 在分组前过滤。

四、HAVING 基础用法示例

我们继续使用 scores 表（5名学生，3门科目，共15条记录，其中一条成绩为NULL）。

1. 简单 HAVING 条件

找出平均分大于85的科目：

SELECT 
    subject,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL   -- 先排除缺考记录
GROUP BY subject
HAVING AVG(score) > 85;

结果示例：

+---------+-----------+
| subject | avg_score |
+---------+-----------+
| 英语    |     89.50 |
| 数学    |     86.70 |
+---------+-----------+

2. 使用多个聚合条件

找出平均分大于80且总考试人数（有效成绩）不少于4人的科目：

SELECT 
    subject,
    COUNT(score) AS exam_count,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY subject
HAVING AVG(score) > 80 AND COUNT(score) >= 4;

3. 使用聚合函数别名（MySQL支持，但并非所有数据库支持）

在 HAVING 中可以直接使用在 SELECT 中定义的别名（但标准SQL不允许，MySQL允许但建议谨慎）：

SELECT 
    subject,
    COUNT(score) AS cnt,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY subject
HAVING cnt >= 4 AND avg_score > 80;   -- 使用别名

4. 结合 ORDER BY 排序

找出平均分最高的科目：

SELECT 
    subject,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject
HAVING AVG(score) > 80
ORDER BY avg_score DESC;

五、高级用法与技巧

1. 在 HAVING 中使用复杂表达式

HAVING 中可以包含任何合法的聚合表达式，甚至可以用 CASE 构造复杂条件。

-- 找出总成绩超过300且平均分大于85的学生
SELECT 
    student_name,
    SUM(score) AS total,
    AVG(score) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY student_name
HAVING SUM(score) > 300 AND AVG(score) > 85;

2. 过滤组内记录数

统计每个学生参加考试的有效科目数，并筛选出参加科目数少于3科的学生（即缺考或未选课）：

SELECT 
    student_name,
    COUNT(score) AS exam_count
FROM scores
GROUP BY student_name
HAVING COUNT(score) < 3;

3. 与 DISTINCT 结合

统计每个学生参加了多少个不同的考试日期（虽然本例中日期固定，但可用）：

SELECT 
    student_name,
    COUNT(DISTINCT exam_date) AS date_count
FROM scores
GROUP BY student_name
HAVING COUNT(DISTINCT exam_date) >= 2;

4. 使用 HAVING 进行数据质量检查

找出那些有成绩为空的学生记录（每个学生应该有三门成绩，如果有学生缺少成绩，则其 COUNT(score) 会小于 COUNT(*)）：

SELECT 
    student_name,
    COUNT(*) AS total_exams,
    COUNT(score) AS valid_exams
FROM scores
GROUP BY student_name
HAVING COUNT(*) > COUNT(score);

六、重要注意事项

1. HAVING 中的列必须是分组列或聚合函数

HAVING 条件中引用的列必须出现在 GROUP BY 中，或者是聚合函数。否则会产生歧义或错误。

-- 错误：score 不是分组列，也不是聚合函数
SELECT student_name, AVG(score)
FROM scores
GROUP BY student_name
HAVING score > 80;   -- 错误，因为每个组有多个score值

2. NULL 值的处理

聚合函数通常忽略 NULL，但 COUNT(*) 会包含 NULL 行。因此要注意 HAVING 条件中使用的聚合函数对 NULL 的敏感性。

3. 性能考虑

• 尽量在 WHERE 中过滤掉不需要的行，减少分组的数据量。

• 对 GROUP BY 和 HAVING 中使用的列建立索引有助于提升性能。

4. 与 WHERE 的顺序

牢记：WHERE 先执行，HAVING 后执行。能用 WHERE 过滤的不要留到 HAVING，因为 HAVING 在分组后处理，效率较低。

七、实战练习与解答

练习题目

基于 scores 表，编写 SQL 实现以下需求：

1. 基础筛选：找出平均分低于85的科目。

2. 多条件筛选：找出总成绩大于250分且考试次数等于3次的学生。

3. 复杂条件：找出那些数学和英语都参加了考试的学生（提示：需要按学生分组，并检查他们是否同时有数学和英语成绩）。

4. 挑战题：找出每个科目中成绩高于该科目平均分的学生（即找出各科“优等生”）。

参考答案

-- 1. 平均分低于85的科目
SELECT 
    subject,
    AVG(score) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject
HAVING AVG(score) < 85;

-- 2. 总成绩大于250且考试次数等于3次的学生
SELECT 
    student_name,
    SUM(score) AS total_score,
    COUNT(score) AS exam_count
FROM scores
GROUP BY student_name
HAVING total_score > 250 AND exam_count = 3;

-- 3. 参加了数学和英语考试的学生
SELECT 
    student_name
FROM scores
GROUP BY student_name
HAVING SUM(CASE WHEN subject = '数学' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN subject = '英语' THEN 1 ELSE 0 END) > 0;

-- 4. 各科中成绩高于该科目平均分的学生
SELECT s1.subject, s1.student_name, s1.score
FROM scores s1
JOIN (
    SELECT subject, AVG(score) AS avg_score
    FROM scores
    WHERE score IS NOT NULL
    GROUP BY subject
) s2 ON s1.subject = s2.subject
WHERE s1.score > s2.avg_score
ORDER BY s1.subject;

八、HAVING 使用要点总结

最佳实践建议

1. 先用 WHERE 过滤行，后用 HAVING 过滤组，以减少数据处理量。

2. 在 HAVING 中使用聚合函数时，可以为其起别名，但要注意数据库兼容性（MySQL 允许别名，标准 SQL 不允许）。

3. 调试时可以先去掉 HAVING 查看所有分组结果，再决定筛选条件。

4. 为经常用于分组和筛选的列建立索引，提升查询性能。

HAVING 子句是分组查询的点睛之笔，它让你能够从聚合结果中提取真正有价值的信息。结合 WHERE、GROUP BY 和 HAVING，你已经可以进行相当复杂的数据分析。