CTE从任意条目中提取整棵树

Question

我正在尝试构建一个CTE，它将撤回所有与数据库中给定的任意记录相关的记录。

Create table Requests (
  Id bigint,
  OriginalId bigint NULL,
  FollowupId bigint NULL
)

insert into Requests VALUES (1, null, 3)
insert into Requests VALUES (2, 1, 8)
insert into Requests VALUES (3, 1, 4)
insert into Requests VALUES (4, 3, null)
insert into Requests VALUES (5, null, null)
insert into Requests VALUES (6, null, 7)
insert into Requests VALUES (7, 6, null)
insert into Requests VALUES (8, 2, null)

OriginalId总是上一条记录(或null)的Id。FollowupId指出了最近的跟踪记录(反过来，它通过OriginalId进行回溯)，可能会被忽略，但是如果它有帮助的话，它就在那里。

我可以使用以下CTE轻松地撤回所有祖先或给定记录的所有后代

;With TransactionList (Id, originalId, followupId, Steps)
AS
(
    Select Id, originalId, followupId, 0 as Steps from requests where Id = @startId
    union all
    select reqs.Id, reqs.originalId, reqs.followupId, Steps + 1 from requests reqs
    inner join TransactionList tl on tl.Id = reqs.originalId --or tl.originalId = reqs.Id
)
SELECT Id from TransactionList

但是，如果我同时使用where子句，就会遇到递归，达到递归限制，它就会终止。即使把这两组组合在一起，我也不能得到整棵树--只有一根树枝。

除了身份证清单之外，我什么都不在乎。他们不需要被分类，也不需要展示他们之间的关系或任何东西。不疼，但没必要。但是，我需要给定树中的每个Id在作为@startId传递时返回相同的列表。

作为我希望看到的一个例子，当@startId设置为任何值1-4或8时，输出应该是这样的：

1
2
3
4
8

无论是6还是7，我都会得到6和7。

Answer 1

你只需创建2个CTE的。

第一个CTE将获得层次结构的根，第二个CTE将使用根ID来获取根的后代。

;WITH cteRoot AS (
    SELECT  *, 0 [Level]
    FROM    Requests
    WHERE   Id = @startId
    UNION ALL
    SELECT  r.*, [Level] + 1
    FROM    Requests r
            JOIN cteRoot cte ON r.Id = cte.OriginalID
),
cteDesc AS (
    SELECT  *
    FROM    cteRoot
    WHERE   OriginalId IS NULL
    UNION ALL
    SELECT  r.*, [Level] + 1
    FROM    Requests r
            JOIN cteDesc cte ON r.OriginalId = cte.Id  
)
SELECT * FROM cteDesc  

SQL Fiddle