使用公用表表达式的递归查询（CTE）

简述：

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

当某个查询引用递归 CTE 时，它即被称为递归查询。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。

递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。CTE是SQL2005新增的功能，SQL2000不支持CTE。

递归 CTE 的结构

递归 CTE 由下列三个元素组成：  

1. 例程的调用

   递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。

   CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。

2. 例程的递归调用

   递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。

3. 终止检查

   终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

示例

use A
go
-- 创建表并插入数据
create table cteTable(ID int identity(1,1),[name] varchar(20),parentID int)
insert into cteTable
select 'D盘',0 union all
select 'ProgramFiles',1 union all
select 'Test1',2 union all
select 'Test2',2 

-- 我们查询ProgramFiles下面所有文件有那些？
;with MyCTE(ID,[name],parentID) as
(
  select ID,[name],parentID
  from cteTable where [name] = 'ProgramFiles' 
  union all
  select e.ID,e.[name],e.parentID
  from cteTable as e,MyCTE as c where e.parentid = c.id
)
select ID,[name],parentID from MyCTE

ID          name                 parentID
--------- -------------------- -----------
2           ProgramFiles         1
3           Test1                2
4           Test2                2

(3 行受影响)

 

CTE执行过程分析

1. 递归 CTE  定义了一个定位点成员和一个递归成员。

2. 定位点成员返回基准结果集 T0。即'ProgramFiles'。

3. 递归成员返回定位点成员结果集中的ProgramFiles的下层文件。这是通过在 cteTable 表和 MyCTE 之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。联接 e.parentid = c.id  返回ProgramFiles 下的文件作为输出。这样，递归成员的第一次迭代返回了 Test1、Test2结果。
4. 假如Test1或者Test2下还有子文件，则会激活递归成员,直到递归结束.

 注：如上如有错误地方，欢迎大家指出。