如何创建一个SQL Server 2005 CTE来返回具有多个父级的子记录的父子关系

Question

我正在尝试使用SQL Server中的CTE，但在让以下场景正常工作方面遇到了瓶颈。我有一个类似于这样的层次结构表：

Node(ID:439)
  Node(ID:123)
    Node(ID:900)        
  Node(ID:56)
    Node(ID:900)

预期结果:

NodeID ParentNodeID
439    0
123    439
900    123
56     439
900    56

基本上，我们有一个父子层次结构表，但有一个微妙的区别。每个子项可能有多个父项。我已经研究了许多关于创建CTE返回父子记录的博客文章和StackOverflow帖子，但它们并没有返回孩子的所有父项，只返回找到的第一个。

这是我尝试过的一个示例CTE：

WITH Hierarchy(NodeID, ParentNodeID)
AS
(
    SELECT 
        T1.NodeID,
          T1.ParentNodeID
    FROM
        ParentChildTable T1
    WHERE
        T1.NodeID = 439

    UNION ALL
    SELECT 
        T1.NodeID,
        T1.ParentNodeID
    FROM
        Heirarchy T1
        INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID
)

（注：由于隐私问题，上述CTE中表名和列名已更改。）

上述CTE运行良好，它找到了从ID：439开始的所有父子记录，但它只找到了项目ID：900的一个父项，尽管它有两个父项。

请问是否可以使用CTE来实现此功能，或者是否有其他SQL方法可以实现？

谢谢。 贾斯。

- Jason Evans

---

请问您能否发布一下您期望的结果是什么样子的？ - gbn

我已经修复了你的层次结构布局，请确认是否解决了问题？ - gbn

嗨。我想要看到的是：NodeID ParentNodeID 439 0 123 439 900 123 56 439 900 56但实际上，我只得到了一个针对项目ID为900的记录，而不是我期望的两条记录。 - Jason Evans

你的CTE中有语法错误 - 你不能在CTE的第二部分将Hierarchy与自身连接。 - Ed Harper

Answer 1

我发现只要我纠正了CTE中的语法错误，这个看起来对我来说是可以正常工作的：

create table #ParentChildTable 
(nodeID int not null
,parentNodeID int not null
)

insert #ParentChildTable 
select 900,56
union all select 900,123
union all select 123,439
union all select 56,439
union all select 439,0

;WITH Heirarchy
AS
(
    SELECT 
        T1.NodeID,
          T1.ParentNodeID
    FROM
        #ParentChildTable T1
    WHERE
        T1.NodeID = 439

    UNION ALL
    SELECT 
        T1.NodeID,
        T1.ParentNodeID
    FROM
        #ParentChildTable T1
        INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID
)
select *
from Heirarchy

返回结果：

NodeID      ParentNodeID
----------- ------------
439         0
123         439
56          439
900         56
900         123

- Ed Harper

---

我需要检查表格中的数据，可能内容有误。CTE看起来应该可以工作，而且你的实验证明它确实有效。如果我再遇到问题，我会快速检查数据并回复您。非常感谢大家的回复。 - Jason Evans

Answer 2

这是我用来找到解决方案的链接。

http://wiki.lessthandot.com/index.php/Using_Common_Table_Expressions_for_Parent-Child_Relationships

编辑 - @Pshimo - 谢谢。这是来自链接的指南。

使用 SQL 2005 就像做梦一样。假设你有以下示例数据：

declare @test table (bunchof uniqueidentifier default newid(), columns uniqueidentifier default newid(), Id int, ParentID int)

insert @test (Id, ParentId)
select 1, null
union all select 5, 1
union all select 15, 2
union all select 16, 5
union all select 27, 16

您想获取1的所有子行（因此，ItemId为5、16、27）

 declare @parentId int
    set @parentId = 1

    ;--last statement MUST be semicolon-terminated to use a CTE
    with CTE (bunchof, columns, Id, ParentId) as
    (
        select bunchof, columns, Id, ParentId
        from @test
        where ParentId = @parentId
        union all
        select a.bunchof, a.columns, a.Id, a.ParentId
        from @test as a
        inner join CTE as b on a.ParentId = b.Id
    )
    select * from CTE

如果你想包含父级：

declare @Id int
set @Id = 1

;--last statement MUST be semicolon-terminated to use a CTE
with CTE (bunchof, columns, Id, ParentId) as
(
    select bunchof, columns, Id, ParentId
    from @test
    where Id = @Id
    union all
    select a.bunchof, a.columns, a.Id, a.ParentId
    from @test as a
    inner join CTE as b on a.ParentId = b.Id
)
select * from CTE

你也可以选择层级深度，如果你喜欢的话：

declare @Id int
set @Id = 1

;--last statement MUST be semicolon-terminated to use a CTE
with CTE (bunchof, columns, Id, ParentId, Depth) as
(
    select bunchof, columns, Id, ParentId, 0
    from @test
    where Id = @Id
    union all
    select a.bunchof, a.columns, a.Id, a.ParentId, b.Depth + 1
    from @test as a
    inner join CTE as b on a.ParentId = b.Id
)
select * from CTE

正如您所见，您正在首先选择包含父ID参数的所有子行的初始记录集。然后，您可以与连接到CTE本身的另一个查询合并，以获取子代的子代（以及其孙代等，直到达到最后的后代行）。需要注意的是，默认递归限制为100，因此在使用时请注意层次结构的深度。您可以使用OPTION（MAXRECURSION）更改递归限制。

 WITH CTE AS (
    ...
    )
    SELECT * FROM CTE OPTION (MAXRECURSION 1000)

- garethb

---

即使链接页面的内容现在包含答案，但将来它可能会更改甚至消失，因此最好的做法是同时发布链接页面中的重要信息。更多信息请参见[答案]。 - Pshemo