Sqlserver 根据父节点ID查询所有子节点

举例：

 

 

 

 

根据父节点ID查询出所有子节点

不含父节点

DECLARE @ParentID int
SET @ParentID='3';
with CTEGetChild as
(
select * from [dbAssetCheck-yili].[dbo].[vFinaceDepartment] where PFDepartmentId=@ParentID
UNION ALL
(SELECT a.* from [dbAssetCheck-yili].[dbo].[vFinaceDepartment] as a inner join
CTEGetChild as b on a.PFDepartmentId=b.Id
)
)
SELECT distinct * FROM CTEGetChild

 

 

 

 

 含父节点

DECLARE @ParentID int
SET @ParentID='3';
with CTEGetChild as
(
select * from [dbAssetCheck-yili].[dbo].[vFinaceDepartment] where PFDepartmentId=@ParentID or Id=@ParentID
UNION ALL
(SELECT a.* from [dbAssetCheck-yili].[dbo].[vFinaceDepartment] as a inner join
CTEGetChild as b on a.PFDepartmentId=b.Id
)
)
SELECT distinct * FROM CTEGetChild