给定hierachy中的任何ID，获取自引用表中的整个ID链

| 我有一个包含以下数据的表： 
+----+----------+
| ID | ParentID |
+----+----------+
| 27 |    0     |
| 38 |    27    | 
| 45 |    38    |
| 86 |    0     |
| 92 |    45    |
| 48 |    86    |
| 62 |    92    |
| 50 |    62    |
-----------------
我希望能够将任何ID传递给存储过程，并获取该给定ID的整个ID链（父母和子女）。 即。如果我通过ID = 45，我应该得到： 
27
38
45
92
62
50
同样，如果我通过ID = 86，我应该得到： 
86
48
任何帮助将不胜感激！     
2020-02-01 2 条评论
分享

没有找到相关结果

    已邀请:

    田眯衅

            您可以使用两个递归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
        
    2020-02-01 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.043174982070923