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