简述:
公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
当某个查询引用递归 CTE 时,它即被称为递归查询。递归查询通常用于返回分层数据,例如:显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件,而那些组件可能还有子组件,或者是其他父级产品的组件)中的数据。
递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。CTE是SQL2005新增的功能,SQL2000不支持CTE。
递归 CTE 的结构
递归 CTE 由下列三个元素组成:
-
例程的调用
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集,所以它们被称为“定位点成员”。
CTE_query_definitions 被视为定位点成员,除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
-
例程的递归调用
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。
-
终止检查
终止检查是隐式的;当上一个调用中未返回行时,递归将停止。
示例
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执行过程分析
- 递归 CTE 定义了一个定位点成员和一个递归成员。
-
定位点成员返回基准结果集 T0。即'ProgramFiles'。
- 递归成员返回定位点成员结果集中的ProgramFiles的下层文件。这是通过在 cteTable 表和 MyCTE 之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。联接 e.parentid = c.id 返回ProgramFiles 下的文件作为输出。这样,递归成员的第一次迭代返回了 Test1、Test2结果。
- 假如Test1或者Test2下还有子文件,则会激活递归成员,直到递归结束.
注:如上如有错误地方,欢迎大家指出。