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

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 --id 部门编号，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 --id 部门编号，PID 上级部门编号

) select * from cte