CTE для возврата всех элементов в иерархии

У меня есть таблица с рекурсивной иерархией (т.е. ID, ParentID). Для любого элемента в этой иерархии я хочу иметь возможность вернуть список всего ВВЕРХ И ВНИЗ по иерархии вместе с уровнем для каждой строки. Предположим, что у родителя может быть только один ребенок.

Возьмем, к примеру, следующее:

ID    ParentID
--------------
1     NULL
2     1
3     2
4     NULL
5     4
6     5

Учитывая идентификатор 1, 2 или 3, я хочу вернуть:

ID    ParentID    Level
-----------------------
1     NULL        1
2     1           2
3     2           3

Я делал это раньше, но не могу вспомнить как. Я знаю, что решение включает в себя CTE, я просто не могу понять это правильно! Любая помощь приветствуется.


sql-server sql-server-2008 common-table-expression recursive-query
person Paul Fleming    schedule 28.09.2012    source источник
comment
возможный дубликат Как найти все дочерние столбцы таблицы в таблице сервера sql?   -  person cadrell0    schedule 28.09.2012
comment
@cadrell0. Мне нужны родители и дети для любого данного ID.   -  person Paul Fleming    schedule 28.09.2012

Ответы (2)


arrow_upward
0
arrow_downward

Самая упрощенная версия запроса CTE, которую я смог придумать, выглядит так:

WITH Ancestry (AncestorID, DescendantID)
AS
(
    SELECT 
        ParentID, ID
    FROM
        dbo.Location
    WHERE
        ParentID IS NOT NULL
UNION ALL
    SELECT 
        P.AncestorID, C.ID
    FROM
        dbo.Location C
    JOIN
        Ancestry P on C.ParentID = P.DescendantID
)
SELECT * FROM Ancestry

Результатом является список всех отношений Предки/Потомки, которые существуют в таблице.

Конечный «SELECT * FROM Ancestry» можно заменить чем-то более сложным для фильтрации, упорядочивания и т. д.

Чтобы включить рефлексивные отношения, запрос можно изменить, добавив две строки в окончательный оператор SELECT:

SELECT * FROM Ancestry
UNION
SELECT ID, ID FROM dbo.Location
person Hutch    schedule 12.05.2015
comment
Я сделал этот запрос в представлении, но не уверен, что это лучшая практика или нет. - person Hutch; 12.05.2015

arrow_upward

arrow_downward

person    schedule
comment
Отлично, работает идеально. Просто чтобы быть нахальным... можете ли вы придумать способ немного оптимизировать это, убрав необходимость во вложенном выборе в конце? - person Paul Fleming; 28.09.2012
comment
Зависит от того, хотите ли вы, чтобы они были в порядке или нет? - person podiluska; 28.09.2012
comment
Я использую результаты этого запроса для возврата дополнительной информации, поэтому в конечном итоге я буду возвращать данные (со связанными данными) в порядке дат. Проще говоря, нет, мне нужен только ID и уровень. - person Paul Fleming; 28.09.2012
comment
вы могли бы сделать select distinct id, parent, 1+ level- (select MIN(level) from cte2) as Level from cte2 (и на самом деле вы все равно можете заказать это по уровню) - person podiluska; 28.09.2012