使用HAVING字句對(duì)分組結(jié)果進(jìn)行過濾。
示例
有一個(gè)表格socre_sheet：
mysql> SELECT * FROM score_sheet;
+----+--------+---------+-------+
| id | name | subject | score |
+----+--------+---------+-------+
| 1 | 張三 | 語文 | 80 |
| 2 | 李四 | 語文 | 90 |
| 3 | 王五 | 語文 | 60 |
| 4 | 王胖子 | 數(shù)學(xué) | 59 |
| 5 | 張王五 | 英語 | 59.9 |
| 6 | 吳彥祖 | 英語 | 99.9 |
| 7 | 郭德綱 | 數(shù)學(xué) | 100 |
| 8 | 郭敬明 | 數(shù)學(xué) | 99 |
| 9 | 郭靖 | 英語 | 70 |
| 10 | 趙四 | 語文 | 61 |
+----+--------+---------+-------+
按科目subject對(duì)這個(gè)表格進(jìn)行分組，統(tǒng)計(jì)選每個(gè)subject的人數(shù)：
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet GROUP BY subject;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 語文 | 4 |
| 數(shù)學(xué) | 3 |
| 英語 | 3 |
+---------+---------------+
過濾分組結(jié)果，只顯示選擇人數(shù)大于3的subject：
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet GROUP BY subject HAVING subject_count>3;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 語文 | 4 |
+---------+---------------+
另外，我們?cè)谇懊鎸W(xué)到，WHERE字句也可以對(duì)數(shù)據(jù)進(jìn)行過濾。
WHERE和HAVING的不同
WHERE是針對(duì)行的過濾。分組時(shí)，WHERE先對(duì)要查詢的數(shù)據(jù)進(jìn)行行過濾，然后再對(duì)過濾后的數(shù)據(jù)進(jìn)行分組。
HAVING對(duì)分組結(jié)果進(jìn)行過濾。先分組，再HAVING過濾。
因此，再語法順序上，WHERE在GROUP BY前，而HAVING在GROUP BY后。
綜合示例
先過濾socre_sheet中分?jǐn)?shù)不小于60分的記錄，再按科目subject對(duì)進(jìn)行分組，統(tǒng)計(jì)選每個(gè)subject的人數(shù)：
mysql> SELECT subject, COUNT(subject) AS subject_count FROM score_sheet WHERE score>=60 GROUP BY subject;
+---------+---------------+
| subject | subject_count |
+---------+---------------+
| 語文 | 4 |
| 英語 | 2 |
| 數(shù)學(xué) | 2 |
+---------+---------------+
再對(duì)以上結(jié)果過濾出subject_count大于3的結(jié)果：
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 |
+---------+---------------+
