SQL HAVING

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

例1
选择所有有超过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