<div style="TEXT-INDENT: 2EM; FONT-SIZE: LARGER;">
在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行受影响)