sql Server 递归查询

插入数据

insert into Department (Id,Name,ParentId)
VALUES (1,'总经办',0),
       (2,'研发部',1),
       (3,'人事部',1),
       (4,'设计部',2),
       (5,'行政部',3)

查询意图：

查询某个部门下所有子部门包括本部门所在层级

WITH CTE AS(
SELECT Id ,0 as LEVEL ,Name
FROM Department
WHERE ParentSId =0 -- 查询的第一层，一般父级的id为0
UNION ALL
SELECT D.Id ,LEVEL+1 ,Name
FROM Department D
INNER JOIN CTE ON CTE.Id=D.Id  --递归层查询
)
SELECT Id,LEVEL，Name 
FROM CTE
ORDER BY LEVEL,Id  ---递归结果查询

结果：