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

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

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

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

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

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

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通过递归方式编写通用函数自动生成连续数字和日期
• SqlServer使用公用表表达式（CTE）实现无限级树形构建
• 使用SqlServer CTE递归查询处理树、图和层次结构
• 在sqlserver中如何使用CTE解决复杂查询问题
• SQL SERVER 2008 CTE生成结点的FullPath
• SQLSERVER2008中CTE的Split与CLR的性能比较
• 使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法
• SQL Server使用T-SQL进阶之公用表表达式(CTE)

解决方法