sql server使用cte实现树结构递归查询

sql server使用cte实现树结构递归查询

 

use xxx

go

Begin

--获得所有有权限的叶子节点

Declare @Tempsubqrys TABLE(

id int,

Parentid int,

planname nvarchar(50),

Grd int

);

 

with subqry(id,Parentid,planname,Grd) as (

select id,Parentid,planname,Grd from T_Sch_Plan where id = 2

union all

select t.id,t.Parentid,t.planname,t.Grd from T_Sch_Plan t,subqry

where t.parentid = subqry.id

)

insert @Tempsubqrys(id,Parentid,planname,Grd) (select * from subqry where subqry.Grd =4 and subqry.id not in (select Planid from T_Sch_Plan_User where Fesid=1));

select * from @Tempsubqrys;

end