sqlserver通过递归查找所有下级或上级部门和用户的操作实例

查找当前用户所在部门的所有下级包括当前部门

with cte as
(
    select id,pid,deptname, 0 as lvl from department
    where id = 2
    union all
    select d.id,d.pid,d.deptname,lvl + 1 from cte c inner join department d
    on c.id = d.pid
)
select * from cte

查找当前用户所在部门的所有上级包括当前部门

with cte as
(
    select id,pid,deptname, 0 as lvl from department
    where id = 2
    union all
    select d.id,d.pid,d.deptname,lvl + 1 from cte c inner join department d
    on c.pid= d.id
)
select * from cte