如何使用CTE遍历层次结构并返回上级节点？

Question

我可以在分层数据模型中找到给定记录的所有子级（请参见下面的代码），但我不确定如何使用给定的子ID遍历回父/子链。有人能指点我如何做吗？在Linq to SQL中是否也可能实现这一点？

WITH TaskHierarchy (TaskID, [Subject], ParentID, HierarchyLevel, HierarchyPath) AS
(
   -- Base case
   SELECT
      TaskID,
      [Subject],
      ParentID,
      1 as HierarchyLevel,
      CONVERT(VARCHAR(MAX),'/') AS HierarchyPath
   FROM Task
   WHERE TaskID = 2

   UNION ALL

   -- Recursive step
   SELECT
      t.TaskID,
      t.Subject,
      t.ParentID,
      th.HierarchyLevel + 1 AS HierarchyLevel,
      CONVERT(varchar(MAX),th.HierarchyPath + CONVERT(VARCHAR(32),t.ParentID) + '/') AS HierarchyPath
   FROM Task t
      INNER JOIN TaskHierarchy th ON
         t.ParentID = th.TaskID
)

SELECT *
FROM TaskHierarchy
ORDER BY HierarchyLevel, [Subject]

- EdenMachine

Answer 1

啊，我想通了：

WITH Hierarchy(TaskID, [Subject], ParentID, IsProject, HLevel)
AS
(
    SELECT 
        TaskID, 
        [Subject], 
        ParentID ,
        IsProject,
        0 as HLevel
    FROM 
        Task 
    WHERE 
        TaskID = 59

    UNION ALL

    SELECT 
        SubDepartment.TaskID, 
        SubDepartment.[Subject], 
        SubDepartment.ParentID ,
        SubDepartment.IsProject,
        HLevel + 1
    FROM 
        Task SubDepartment
    INNER JOIN 
        Hierarchy ParentDepartment
    ON 
        SubDepartment.TaskID = ParentDepartment.ParentID 
)

SELECT 
    TaskID, 
    [Subject], 
    ParentID,
    IsProject,
    HLevel
FROM  
    Hierarchy
ORDER BY
    HLevel DESC

- EdenMachine

---

谢谢您发布解决方案！它节省了我数小时的时间！ - Chuck Conway