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
