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

Я экспериментирую с 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

Итак, в основном у нас есть таблица иерархии родитель-потомок с одной тонкой разницей. У каждого ребенка потенциально может быть более одного родителя. Я изучил множество статей в блогах и сообщений StackOverflow о создании 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, но находит только одного родителя для элемента ID: 900, хотя у него есть два родителя.

Может ли кто-нибудь сообщить мне, возможно ли это с помощью CTE или есть другой способ SQL?

Ваше здоровье. Jas.


sql-server common-table-expression sql-server-2005 parents
person Community    schedule 20.03.2009    source источник
comment
Не могли бы вы опубликовать, как вы ожидаете, будут выглядеть результаты?   -  person gbn    schedule 20.03.2009
comment
Я исправил вашу иерархию, пожалуйста?   -  person gbn    schedule 20.03.2009
comment
Всем привет. Я бы хотел увидеть следующее: NodeID ParentNodeID 439 0 123 439 900 123 56 439 900 56 Но я получаю только одну запись для идентификатора элемента: 900, а не две записи, которые я ожидал.   -  person    schedule 20.03.2009
comment
В вашем CTE есть синтаксическая ошибка - вы не можете присоединить иерархию к самой себе во второй части CTE   -  person Ed Harper    schedule 20.03.2009

Ответы (2)


arrow_upward
5
arrow_downward

Похоже, у меня это сработало, как только я исправил синтаксическую ошибку в вашем 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
person Ed Harper    schedule 20.03.2009
comment
Я собираюсь проверить данные в таблице, может быть что-то не так с содержимым. CTE выглядит так, как будто он должен работать, и ваш эксперимент действительно доказал, что это так. Быстро проверим данные и свяжусь с вами, если снова застряну. Большое спасибо за ответы людям. - person ; 20.03.2009

arrow_upward
1
arrow_downward

Это ссылка, которую я использовал, чтобы найти решение ..

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)
person garethb    schedule 08.02.2013
comment
Даже если в содержимом связанной страницы сейчас есть ответ, в будущем он может быть изменен или даже исчез, поэтому рекомендуется также публиковать важную информацию со связанной страницы. Подробнее см. Как ответить. - person Pshemo; 08.02.2013