递归CTE是Sql Server2005 中很重要的T-Sql 增强之一。SqlServer 最终还是通过纯基于集合的查询实现了递归查询。可以受益于递归查询的任务和操作包括操作图，树，层次结构等。这儿介绍一个递归CTE。

数据库：Northwind

表：Employees

要求：根据EmployeeID和ReportsTo属性维护的层次关系，返回输入员工和该员工所有级别的下属，包括属性： EmployeeID , ReportsTo , FirstName , LastName

下面是递归CTE

 1 

WITH

 EmpsCTE 

AS

 

 2 

(

 3 

    

SELECT

 EmployeeID , ReportsTo , FirstName , LastName

 4 

    

FROM

 dbo.Employees

 5 

    

WHERE

 EmployeeID 

=

 

5

 6 

 7 

    

Union

 

ALL

 8 

 9 

    

SELECT

 EMP.EmployeeID,EMP.ReportsTo,EMP.FirstName,EMP.LastName

10 

    

FROM

 EmpsCTE 

AS

 MGR

11 

        

JOIN

 dbo.Employees 

AS

 EMP

12 

            

ON

 EMP.ReportsTo 

=

 MGR.EmployeeID

13 

)

14 

 

15 

SELECT

  

*

 

FROM

 EmpsCTE;

 

结果返回如下：

 

递归CTE最少包含两个查询（成员），CTE主题中的第一个查询被称为定位点成员（Anchor Member） 。定位点成员只是一个返回有效表的查询，用于递归的基础或定位点。在上例中定位点成员直接返回输入根员工（EmployeeID = 5）所在的行。CTE主体中的第二个查询被称为递归成员。使该查询成为递归成员的是对CTE名称（EmpsCTE）的递归引用。注意该引用不同于在外部查询中对CTE名称的引用。外部查询中的引用得到由该CTE返回的最终结果集，不包含递归。而内部引用是在该CTE的结果表确定之前使用的，它是触发递归的关键。

CTE中没有显示的递归终止检查，只要递归成员返回空集就停止递归。因为第一次调用该递归成员返回的结果集（员工6，7，9），它将再次被调用。第二次调用这递归成员时，CTE名称引用表示由上一次调用递归成员返回的结果集（员工6，7，9）。因为这些员工没有下属，第二次调用递归成员产生一个空集，递归终止。