Как создать SQL Server 2005 CTE для возврата записей родитель-потомок, для детей с несколькими родителями

Question

Я экспериментирую с CTE в SQL Server, но зашел в тупик, заставив работать следующий сценарий. У меня есть иерархическая таблица, подобная этой:

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

Итак, в основном у нас есть таблица иерархии родитель-потомок, с одним тонким отличием. Каждый ребенок потенциально может иметь более одного родителя. Я исследовал много статей в блогах и постах Stackru о создании CTE, которые возвращают записи родитель-потомок, но они не возвращают всех родителей для детей, только первую найденную.

Вот пример 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, но он находит только одного родителя для идентификатора элемента: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

Как вы можете видеть, что вы делаете здесь, сначала выбираете исходный набор записей, который содержит все дочерние строки для параметра родительского идентификатора. Затем вы можете объединиться с другим запросом, который присоединяется к самому CTE, чтобы получить детей детей (и их внуков и т. Д., Пока вы не достигнете последней строки-потомка. Важно отметить, что предел рекурсии по умолчанию равен 100, поэтому платите обращая внимание на глубину вашей иерархии, вы можете изменить предел рекурсии, используя OPTION (MAXRECURSION)

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