公用表表达式(CTE)

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

内联格式：

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

外部格式：

WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

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

WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

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

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 2
  
  UNION ALL
  
  SELECT C.empid, C.mgrid, C.firstname, C.lastname
  FROM EmpsCTE AS P
    JOIN HR.Employees AS C
      ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

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