如何创建 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 方法可以做到这一点？

干杯。 贾斯。

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

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)