递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

递归执行的语义如下：

1. 将 CTE 表达式拆分为定位点成员和递归成员。
   
2. 运行定位点成员，创建第一个调用或基准结果集 (T0)。
   
3. 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
   
4. 重复步骤 3，直到返回空集。
   
5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。
   

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO

1. 递归 CTE DirectReports 定义了一个定位点成员和一个递归成员。
   
2. 定位点成员返回基准结果集 T0。这就是公司中的最高级雇员，即不向经理报告的雇员。 
   以下是定位点成员返回的结果集： 
   

   ManagerID EmployeeID Title                                   Level
       --------- ---------- --------------------------------------- ------
       NULL      109        Chief Executive Officer                 0
       

3. 递归成员返回定位点成员结果集中的雇员的直接下属。这是通过在 Employee 表和 DirectReports CTE 之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。利用 CTE DirectReports 中的雇员作为输入 (Ti)，联接 (Employee.ManagerID = DirectReports.EmployeeID) 返回经理为 (Ti) 的雇员作为输出 (Ti+1)。这样，递归成员的第一次迭代返回了以下结果集： 
   

   ManagerID EmployeeID Title                                   Level
       --------- ---------- --------------------------------------- ------
       109       12         Vice President of Engineering           1
       

4. 重复激活递归成员。递归成员的第二次迭代使用步骤 3 中的单行结果集（包含 EmployeeID12）作为输入值，并返回以下结果集： 
   

   ManagerID EmployeeID Title                                   Level
       --------- ---------- --------------------------------------- ------
       12        3          Engineering Manager                     2
       

   递归成员的第三次迭代使用上面的单行结果集（包含 EmployeeID3)）作为输入值，并返回以下结果集： 
   

   ManagerID EmployeeID Title                                   Level
       --------- ---------- --------------------------------------- ------
       3         4          Senior Tool Designer                    3
       3         9          Design Engineer                         3
       3         11         Design Engineer                         3
       3         158        Research and Development Manager        3
       3         263        Senior Tool Designer                    3
       3         267        Senior Design Engineer                  3
       3         270        Design Engineer                         3
       

   递归成员的第四次迭代使用 EmployeeID 值 4、9、11、158、263、267 和 270 的上一个行集作为输入值。 
   重复此过程，直到递归成员返回一个空结果集。
   
5. 正在运行的查询返回的最终结果集是定位点成员和递归成员生成的所有结果集的并集。 
   以下是示例返回的完整结果集：
   

   ManagerID EmployeeID Title                                   Level
       --------- ---------- --------------------------------------- ------
       NULL      109        Chief Executive Officer                 0
       109       12         Vice President of Engineering           1
       12        3          Engineering Manager                     2
       3         4          Senior Tool Designer                    3
       3         9          Design Engineer                         3
       3         11         Design Engineer                         3
       3         158        Research and Development Manager        3
       3         263        Senior Tool Designer                    3
       3         267        Senior Design Engineer                  3
       3         270        Design Engineer                         3
       263       5          Tool Designer                           4
       263       265        Tool Designer                           4
       158       79         Research and Development Engineer       4
       158       114        Research and Development Engineer       4
       158       217        Research and Development Manager        4
       (15 row(s) affected)