给定层次结构中的任何 ID，获取自引用表中的整个 ID 链

Question

我有一个包含以下数据的表：

+----+----------+
| ID | ParentID |
+----+----------+
| 27 |    0     |
| 38 |    27    | 
| 45 |    38    |
| 86 |    0     |
| 92 |    45    |
| 48 |    86    |
| 62 |    92    |
| 50 |    62    |
-----------------

我希望能够将任何 ID 传递给存储过程并获取整个 该给定 ID 的 ID 链（父级和子级）。

IE。如果我通过 ID = 45，我应该得到：

27
38
45
92
62
50

同样，如果我通过 ID = 86，我应该得到：

86
48

任何帮助将不胜感激！

Answer 1

您可以使用两个递归 CTE。第一个找到根节点，第二个构建链。

declare @T table(ID int, ParentID int)

insert into @T values (27,  0), (38, 27), (45, 38), (86,  0),
                      (92, 45), (48, 86), (62, 92), (50, 62)    

declare @ID int = 45

;with cte1 as
(
  select T.ID, T.ParentID, 1 as lvl
  from @T as T
  where T.ID = @ID
  union all
  select T.ID, T.ParentID, C.lvl+1
  from @T as T
    inner join cte1 as C
      on T.ID = C.ParentID
),
cte2 as
(
  select T.ID, T.ParentID
  from @T as T
  where T.ID = (select top 1 ID
                from cte1
                order by lvl desc)
  union all
  select T.ID, T.ParentID
  from @T as T
    inner join cte2 as C
      on T.ParentID = C.ID
)
select ID
from cte2

版本 2

更短一些，查询计划建议更有效，但如果不测试真实数据，你永远不会知道。

;with cte as
(
  select T.ID, T.ParentID, ','+cast(@ID as varchar(max)) as IDs
  from @T as T
  where T.ID = @ID
  union all
  select T.ID, T.ParentID, C.IDs+','+cast(T.ID as varchar(10))
  from @T as T
    inner join cte as C
      on (T.ID = C.ParentID or
          T.ParentID = C.ID) and
          C.IDs+',' not like '%,'+cast(T.ID as varchar(10))+',%'
)
select ID
from cte