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 美工