公用表表达式(CTE)

下面看在CTE中分配列别名的两种格式：内联格式和外部格式。

内联格式：

1 WITH C AS
2 (
3   SELECT YEAR(orderdate) AS orderyear, custid
4   FROM Sales.Orders
5 )
6 SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
7 FROM C
8 GROUP BY orderyear;

外部格式：

1 WITH C(orderyear, custid) AS
2 (
3   SELECT YEAR(orderdate), custid
4   FROM Sales.Orders
5 )
6 SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
7 FROM C
8 GROUP BY orderyear;

定义多个CTE只需要在同一个WITH子句中用逗号把它们分隔开即可。每个CTE可以引用在它前面定义的所有CTE，而外部查询则可以引用所有CTE，例如：

 1 WITH C1 AS
 2 (
 3   SELECT YEAR(orderdate) AS orderyear, custid
 4   FROM Sales.Orders
 5 ),
 6 C2 AS
 7 (
 8   SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
 9   FROM C1
10   GROUP BY orderyear
11 )
12 SELECT orderyear, numcusts
13 FROM C2
14 WHERE numcusts > 70;

CTE还可以实现对数据的递归查询。以下代码演示了如何使用递归CTE来返回有关某个雇员(Don Funk，雇员ID为2)及其所有各级(直接或间接)下属的信息：

 1 WITH EmpsCTE AS
 2 (
 3   SELECT empid, mgrid, firstname, lastname
 4   FROM HR.Employees
 5   WHERE empid = 2
 6   
 7   UNION ALL
 8   
 9   SELECT C.empid, C.mgrid, C.firstname, C.lastname
10   FROM EmpsCTE AS P
11     JOIN HR.Employees AS C
12       ON C.mgrid = P.empid
13 )
14 SELECT empid, mgrid, firstname, lastname
15 FROM EmpsCTE;

为了安全起见，SQL Server默认把递归成员最多可以调用的次数限制为100次，当递归成员的调用次数超过这个值时，代码将会因递归失败而终止运行。如要修改默认最大递归次数，可以在外部查询的最后指定OPTION(MAXRECURSION n)提示(hint)，这里的n的范围是0到32767之间的整数。如果想要去掉此限制，可将MAXRECURSION设为0。