SQL Server 公用表表达式(CTE)实现递归
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server 公用表表达式(CTE)实现递归，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4262字，纯文字阅读大概需要7分钟。
内容图文
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：lv=@level-1 是重点，不然会进入死循环，作用就是限制只插入一次。
如果需要限制循环的次数，即递归的层数，那么只需要在 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)实现递归
标签：and 死循环 union all 判断语句 fill 技术分享 declare div too
本文系统来源：https://www.cnblogs.com/asdyzh/p/9818682.html
内容总结
以上是互联网集市为您收集整理的SQL Server 公用表表达式(CTE)实现递归全部内容，希望文章能够帮你解决SQL Server 公用表表达式(CTE)实现递归所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。