递归CTE

递归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）。因为这些员工没有下属，第二次调用递归成员产生一个空集，递归终止。