sqlserver通过递归查找所有下级或上级

递归查找所有孩子节点,包括当前节点

--查找当前用户所在部门的所有下级包括当前部门
with children as
(
    select ObjectId,Pid,Name, 0 as lvl from SysStruct
    where ObjectId = 2
    union all
    select d.ObjectId,d.Pid,d.Name,lvl + 1 from children c inner join SysStruct d
    on c.ObjectId = d.Pid --ObjectId 部门编号,Pid 上级部门编号
)
select * from children

递归查找所有上级节点,包括当前节点

-- 查找当前用户所在部门的所有上级包括当前部门
with parent as
(
    select ObjectId,Pid,Name, 0 as lvl from SysStruct
    where ObjectId = '1'
    union all
    select d.ObjectId,d.Pid,d.Name,lvl + 1 from parent p inner join SysStruct d
    on p.Pid= d.ObjectId --ObjectId 部门编号,Pid 上级部门编号
) select * from parent
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页