使用HAVING字句对分组结果进行过滤。
示例
有一个表格socre_sheet:
mysql> SELECT * FROM score_sheet;
+----+--------+---------+-------+
| id | name | subject | score |
+----+--------+---------+-------+
| 1 | 张三 | 语文 | 80 |
| 2 | 李四 | 语文 | 90 |
| 3 | 王五 | 语文 | 60 |
| 4 | 王胖子 | 数学 | 59 |
| 5 | 张王五 | 英语 | 59.9 |
| 6 | 吴彦祖 | 英语 | 99.9 |
| 7 | 郭德纲 | 数学 | 100 |
| 8 | 郭敬明 | 数学 | 99 |
| 9 | 郭靖 | 英语 | 70 |
| 10 | 赵四 | 语文 | 61 |
+----+--------+---------+-------+
按科目subject对这个表格进行分组,统计选每个subject的人数:
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet GROUP BY subject;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 语文 | 4 |
| 数学 | 3 |
| 英语 | 3 |
+---------+---------------+
过滤分组结果,只显示选择人数大于3的subject:
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet GROUP BY subject HAVING subject_count>3;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 语文 | 4 |
+---------+---------------+
另外,我们在前面学到,WHERE字句也可以对数据进行过滤。
WHERE和HAVING的不同
WHERE是针对行的过滤。分组时,WHERE先对要查询的数据进行行过滤,然后再对过滤后的数据进行分组。
HAVING对分组结果进行过滤。先分组,再HAVING过滤。
因此,再语法顺序上,WHERE在GROUP BY前,而HAVING在GROUP BY后。
综合示例
先过滤socre_sheet中分数不小于60分的记录,再按科目subject对进行分组,统计选每个subject的人数:
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet WHERE score>=60 GROUP BY subject;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 语文 | 4 |
| 英语 | 2 |
| 数学 | 2 |
+---------+---------------+
再对以上结果过滤出subject_count大于3的结果:
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet WHERE score>=60 GROUP BY subject HAVING subject_count>3;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 语文 | 4 |
+---------+---------------+