SQL递归查询(with cte as)

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

Id          Pid         DeptName
----------- ----------- --------------------------------------------------
1           0           总部
2           1           研发部
3           1           测试部
4           1           质量部
5           2           小组1
6           2           小组2
7           3           测试1
8           3           测试2
9           5           前端组
10          5           美工

Id          Pid         DeptName                                           lvl
----------- ----------- -------------------------------------------------- -----------
2           1           研发部                                                0
5           2           小组1                                                1
6           2           小组2                                                1
9           5           前端组                                                2
10          5           美工                                                 2

(5 行受影响)