SQL Server 公用表表达式(CTE)实现递归的方法

公用表表达式简介：

公用表表达式 (cte) 可以认为是在单个 select、insert、update、delete 或 create view 语句的执行范围内定义的临时结果集。cte 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，公用表表达式 (cte) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 cte。递归 cte 是一个重复执行初始 cte 以返回数据子集直到获取完整结果集的公用表表达式。

下面先创建一个表，并插入一些数据：

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' union 
select '2','管理员a','1' union 
select '3','管理员b','2' union 
select '4','会员aa','2' union 
select '5','会员ab','2' union 
select '6','会员ba','3' union 
select '7','会员bb','3' union 
select '8','用户aaa','4' union 
select '9','用户bba','7' 
-- 创建一个复合聚集索引
create clustered index clu_role_cte_index
on role_cte(id,parentid)
with
(
 pad_index=on,
 fillfactor=50,
 drop_existing=off,
 statistics_norecompute=on
)
select * from role_cte

查找指定节点的所有子孙节点：

使用普通 sql 语句实现：

declare @level int
declare @node int
declare @restab table
(
 node int not null,
 lv  int not null 
)
set @level=0  -- 表示初始的等级
set @node=3   --表示初始的节点id,即从指定的哪个节点开始查找
insert into @restab    -- 为表变量插入初始的数据
select id,@level 
from role_cte 
where id=@node
while(@@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（内连接）和自连接
end
select 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： 是重点，不然会进入死循环，作用就是限制只插入一次。

如果需要限制循环的次数，即递归的层数，那么只需要在 while 条件里面添加一个限制即可。如下：

declare @level int
declare @node int
declare @num int
declare @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,@level 
from role_cte 
where id=@node
while(@@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（内连接）和自连接
end
select a.node,b.name,a.lv 
from @restab a 
left join role_cte b on a.node=b.id

当然，如果指定了循环次数，就可以不用 while 判断语句的 @@rowcount>0 了。

使用 sql cte 实现：

declare @node int 
set @node=3;
with temp_cte
as
(
 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 控制递归的层数，与上面类似。如下：

declare @node int 
declare @num int
set @node=3;
set @num=1;
with temp_cte
as
(
 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 语句实现：

declare @level int
declare @node int
declare @num int
declare @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=@node
end
select a.node,b.name,a.lv 
from @restab a 
left join role_cte b on a.node=b.id

使用 sql cte 实现：

declare @node int 
declare @num int
set @node=8;
set @num=2;
with temp_cte
as
(
 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)实现递归的方法，希望对大家有所帮助