--根据节点ID获取所有子节点

WITH AreaTree AS 
(
    SELECT Id,Name,OrderNum,ParentCategoryId,Status,0 L from dbo.D_DeviceLocation
    where Id = 'c92b9a28-fbc2-480e-a3cf-4d0520d1e3cf'--需要查找的节点  
    UNION ALL 
    SELECT location.Id,location.Name,location.OrderNum,location.ParentCategoryId,location.Status,L+1 from AreaTree
    JOIN dbo.D_DeviceLocation location on AreaTree.id = location.ParentCategoryId
    WHERE location.Status != -1
)
SELECT * FROM AreaTree;

--根据节点ID获取所有父节点

WITH T 
AS(  
    SELECT Id,ParentCategoryId,Name,0 L FROM dbo.D_DeviceLocation WHERE Id='0'
    UNION ALL  
    SELECT U.Id,U.ParentCategoryId,U.Name,L+1    
    FROM dbo.D_DeviceLocation U INNER JOIN T ON U.Id=T.ParentCategoryId   
)  
SELECT * FROM T
Last modification:April 27th, 2020 at 02:48 pm
坚持技术分享,您的支持将鼓励我继续创作!