有的时候需要对部分分组进行过滤,比如只检索人数大于1 的年龄段,有的开发人员会使用下面的SQL语句:
SELECT FAge, COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge WHERE COUNT(*) > 1
在数据库系统中执行上面的SQL语句时,数据库系统会提示语法错误。这是因为聚合函数不能再WHERE语句中使用,必须使用HAVING子句来代替,比如
SELECT FAge, COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) > 1
+------+----------------+
| fage | CountOfThisAge |
+------+----------------+
| 25 | 3 |
| 28 | 2 |
+------+----------------+
HAVING 语句也可以向WHERE语句一样使用复杂的过滤条件,比如下面SQL语句用来检索人数为1个或者3个的年龄段:
1)or的操作符方式
SELECT FAge, COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) =1 OR COUNT(*)=3
2)IN操作符方式
SELECT FAGE, COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) IN(1,3)
注意:
1. HAVING语句能够使用的语法和WHERE几乎是一样的,不过使用WHERE的时候GROUP BY子句要位于WHERE 子句之后;而使用HAVING子句的时候GROUP BY 子句要位于HAVING 子句之前!比如下面的SQL是错误的:
SELECT FAge, COUNT(*) AS CountOfThisAge From T_Employee HAVING COUNT(*) GROUP BY FAge
2. 在HAVING语句中不能包含未分组的列名,比如下面的SQL语句是错误的:
SELECT FAge, COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING FName IS NOT NULL
此时需要用WHERE语句来代替HAVING, 修改后的SQL语句如下:
SELECT FAge, COUNT(*) AS CountOfThisAge FROM T_Employee WHERE FNam IS NOT NULL GROUP BY FAge
+----------+--------+------+---------+-------------+-------------------+
| FNumber | FName | FAge | FSalary | FSubCompany | FDepartment |
+----------+--------+------+---------+-------------+---------------+
| DEV001 | Tom | 25 | 8300.00 | Beijing | Development |
| DEV002 | Jerry | 28 | 2300.00 | ShenZhen | Development |
| DEV003 | Potter | NULL | 3333.00 | Guangzhou | Development |
| HR001 | Jane | 23 | 4300.00 | Beijing | HumanResource |
| HR002 | Tina | 25 | 3300.00 | Beijing | HumanResource |
| IT001 | Smith | 28 | 5500.00 | Beijing | InfoTech |
| IT002 | NULL | 27 | 2800.00 | ShenZhen | InfoTech |
| SALES001 | Timmy | 25 | 5300.00 | Beijing | Sales |
| SALES002 | Stone | 35 | 8300.00 | Beijing | Sales |
+----------+--------+------+---------+-------------+-------------------+