在T-SQL中,SELECT语句的基本语法规则如下:
SELECT [ALL|DISTINCT] [TOP (<expression>) [PERCENT] [WITH TIES]] <column list>
[FROM ] [WHERE <restrictive condition>]
[GROUP BY <column name or expression using a column in the select list>]
[HAVING <restrictive condition based on the group by results>]
[ORDER BY <column list>]
[[FOR XML {RAW|AUTO|EXPLICIT|PATH [(<element>)]}[, XMLDATA][, ELEMENTS]
[, BINARY base 64]]
[OPTION (<query hint>, [, ...n])]
其中HAVING子句是给分组设置条件的,与WHERE子句的功能一样,只是用在不同的地方。HAVING子句仅用于带有GROUP BY子句的查询语句中。WHERE子句应用于每一行(在变成一组的某一部分之前),而HAVING子句应用于分组的聚合值。如果要将查询条件放到分组之后,可以使用HAVING子句。
以下是两个对比例子:
SELECT ManagerID AS Manager, COUNT(*) AS Reports
FROM HumanResources.Employee
WHERE EmployeeID != 5
GROUP BY ManagerID;
返回的结果为:
Manager Reports
--------------------------------
NULL 1
1 3
4 2
5 4
(4 行受影响)
SELECT ManagerID AS Manager, COUNT(*) AS Reports
FROM HumanResources.Employee
WHERE EmployeeID != 5
GROUP BY ManagerID
HAVING COUNT(*) > 3;
结果为:
Manager Reports
-----------------------------
5 4
(1行受影响)