公用表表达式简介：
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
下面先创建一个表，并插入一些数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
create table Role_CTE( Id int not null, Name nvarchar(32) not null, ParentId int not null)insert into Role_CTE(Id,Name,ParentId)select '1','超级管理员','0' unionselect '2','管理员A','1' unionselect '3','管理员B','2' unionselect '4','会员AA','2' unionselect '5','会员AB','2' unionselect '6','会员BA','3' unionselect '7','会员BB','3' unionselect '8','用户AAA','4' unionselect '9','用户BBA','7'-- 创建一个复合聚集索引create clustered index Clu_Role_CTE_Indexon Role_CTE(Id,ParentId)with( pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on)select * from Role_CTE |
查找指定节点的所有子孙节点：
使用普通 sql 语句实现：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
declare @level intdeclare @node intdeclare @ResTab table( node int not null, lv int not null)set @level=0 -- 表示初始的等级set @node=3 --表示初始的节点ID,即从指定的哪个节点开始查找insert into @ResTab -- 为表变量插入初始的数据select Id,@levelfrom Role_CTE where Id=@nodewhile(@@ROWCOUNT>0)begin set @level=@level+1 insert into @ResTab select b.Id,@level from @ResTab a join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等于 inner join（内连接）和自连接endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id |
以上是根据指定节点ID(3)，查找父节点ID(即字段 ParentId)等于指定的节点ID，如果有就插入，并继续循环。
PS：lv=@level-1 是重点，不然会进入死循环，作用就是限制只插入一次。
如果需要限制循环的次数，即递归的层数，那么只需要在 while 条件里面添加一个限制即可。如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv int not null)set @level=0 -- 表示初始的等级set @node=3 --表示初始的节点ID,即从指定的哪个节点开始查找set @num=1 -- 指定递归层级，即循环的次数insert into @ResTab -- 为表变量插入初始的数据select Id,@levelfrom Role_CTE where Id=@nodewhile(@@ROWCOUNT>0 and @level<@num)begin set @level=@level+1 insert into @ResTab select b.Id,@level from @ResTab a join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等于 inner join（内连接）和自连接endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id |
当然，如果指定了循环次数，就可以不用 while 判断语句的 @@rowcount>0 了。
使用 SQL CTE 实现：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
declare @node intset @node=3;with temp_cteas( select Id,Name,0 lv -- 查询出“根节点”，即指定的起始节点 from Role_CTE where Id=@node union all select b.Id,b.Name,a.lv+1 from temp_cte a join Role_CTE b on a.Id=b.ParentId)select * from temp_cte |
使用 CTE 控制递归的层数，与上面类似。如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
declare @node intdeclare @num intset @node=3;set @num=1;with temp_cteas( select Id,Name,0 lv -- 查询出“根节点”，即指定的起始节点 from Role_CTE where Id=@node union all select b.Id,b.Name,a.lv+1 from temp_cte a join Role_CTE b on a.Id=b.ParentId and a.lv<@num --控制递归层数)select * from temp_cte |
查找指定节点的所有祖先节点：
使用普通 sql 语句实现：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv int not null)set @level=0 -- 表示初始的等级set @node=8 --表示初始的节点ID,即从指定的哪个节点开始查找set @num=2 -- 指定递归层级，即循环的次数while(@level<=@num and @node is not null) -- 如果为空就表示没有查到父级了begin insert into @ResTab select @node,@level set @level=@level+1 select @node=ParentId from Role_CTE where Id=@nodeendselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id |
使用 SQL CTE 实现：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
declare @node intdeclare @num intset @node=8;set @num=2;with temp_cteas( select Id,Name,ParentId,0 lv -- 查询出“根节点”，即指定的起始节点 from Role_CTE where Id=@node union all select b.Id,b.Name,b.ParentId,a.lv+1 from temp_cte a join Role_CTE b on a.ParentId=b.Id and a.lv < @num --控制递归层数)select * from temp_cte |
以上所述是小编给大家介绍的SQL Server 公用表表达式(CTE)实现递归的方法，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的，在此也非常感谢大家对服务器之家网站的支持！
原文链接：http://www.cnblogs.com/Brambling/archive/2017/04/28/6778814.html
