SQL HAVING子句与SQL WHERE子句类似。不同的是SQL HAVING子句用于合计函数而SQL WHERE子句不能用于合计函数。
SQL HAVING子句语法
SELECT ColumnName1, ColumnName2, Aggregate_function (ColumnName) FROM TableName WHERE conditions GROUP BY ColumnName1, ColumnName2 HAVING 合计函数 (ColumnName) operator value
注：合计函数可以是SQL MAX, MIN, SUM, COUNT 或AVG。
SQL HAVING子句范例
数据表： Employees
| EmployeeId | FirstName | LastName | Department | Salary |
|---|---|---|---|---|
| 203 | Mazojys | Fxoj | Finance | 78000 |
| 204 | Jozzh | Lnanyo | Finance | 45800 |
| 205 | Syllauu | Dfaafk | Finance | 57000 |
| 206 | Gecrrcc | Srlkrt | Finance | 62000 |
| 302 | Jssme | Bdnaa | Development | 75000 |
| 303 | Dnnaao | Errllov | Development | 55000 |
| 304 | Tyoysww | Osk | Development | 49000 |
选择所有有超过3名职员(employee)的部门：
SELECT Department, COUNT(EmployeeId) FROM Employees GROUP BY Department HAVING COUNT(EmployeeId)>3
结果会类似于：
| Department | COUNT(EmployeeId) |
|---|---|
| Finance | 4 |
例2
选择所有最低薪水超过$46,000的部门：
SELECT Department, MIN(Salary) FROM Employees GROUP BY Department HAVING MIN(Salary)>46000
结果会类似于：
| Department | MIN(Salary) |
|---|---|
| Development | 49000 |
I want to say that The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
i am a sql beginner and really like your blog and articles. haven’t posted anything but had to say thanks
Hi, sqlfan. This is my first visit to your blog, I have found your blog link through webhosting talk forum.
I have read some of your blog post not get what clearly it is as I am not that much technical. You have done lot of hard work and really you are sql fan. A nice informative sql tutorial. Keep it up.
I had never heard of the HAVING clause. Previously I would simply string multiple WHERE clauses together to get the desired task done, but it really didn’t look pretty and I suppose it would put more strain on the database?
The thing is nobody actually uses the HAVING clause these days and there aren’t many situations where you would actually have to use it.
I haven’t really used this HAVING clause. Actually I haven’t heard of it until now. But anyway, thanks for posting this very informative blog. I might use it next time.
HAVING clause has pretty much been ignored for that past 10 years, except maybe in highly specialized applications.
SQL HAVING, more like SQL WANTING.