下面看在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。
原文：http://www.cnblogs.com/laixiancai/p/4351977.html