SQL Server 关键字

SQL Server (Transact-SQL) HAVING 关键字用于指定聚合函数的条件。请注意,SQL Server (Transact-SQL) WHERE 关键字不能用于通过聚合函数指定条件。

语法

在 SQL Server (Transact-SQL) 中使用 HAVING 关键字的语法如下:

SELECT column_name(s)
FROM table_name
WHERE condition(s)
GROUP BY column_name(s)
HAVING condition(s)
ORDER BY column_name(s); 

请注意,HAVING 子句必须遵循查询中的 GROUP BY 子句,如果使用,还必须位于 ORDER BY 子句之前。

示例:

考虑一个包含名为 Employee 和 Bonus_Paid表的数据库 包含以下记录:

表 1:Employee 表

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryLondon242750
3JoLondon272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangAmsterdam282800

表 2:Bonus_Paid 表

EmpIDBonus
1500
2400
3450
4550
5400
6600

获取包含员工人数的列表 (来自每个城市的员工表),并且仅包括拥有超过一名员工的城市,查询如下:

SELECT COUNT(EmpID) AS Number_of_Employee, City  
FROM Employee
GROUP BY City
HAVING COUNT(EmpID) > 1; 

这将产生如下所示的结果:

Number_of_EmployeeCity
2Amsterdam
3London

要根据员工数量按降序排列上述表格,查询将是:

SELECT COUNT(EmpID), City  
FROM Employee
GROUP BY City
HAVING COUNT(EmpID) > 1
ORDER BY COUNT(EmpID) DESC; 

查询结果将是:

Number_of_EmployeeCity
3London
2Amsterdam

在 JOIN 中使用 HAVING 关键字:要获取包含每个城市向员工支付的奖金的列表,并且仅包含拥有超过一名员工的城市,查询如下所示:

SELECT A.City, SUM(B.Bonus) AS BonusAmount
FROM Employee AS A
INNER JOIN Bonus_Paid AS B
ON A.EmpID = B.EmpID
GROUP BY City
HAVING COUNT(A.EmpID) > 1; 

以下代码的结果将是:

CityBonusAmount
Amsterdam1150
London1350