SQL Server - получить корневой узел из иерархической таблицы

Мне нужно получить корневой узел иерархической таблицы (имеет PARENT_ID = NULL):

ID | PARENT_ID 
60   NULL
21   60
11   NULL
23   11
24   21
25   23
14   24
13   25

Я хочу этот результат:

ID | PARENT_ID | ROOT_ID |
-------------------------
1      NULL       NULL
2         1          1
3         1          1
4         2          1
5         4          1
6       NULL    NULL
7         6          6
8         7          6

Является ли CTE хорошим способом? Как я могу его создать?

Очевидно, я мог бы иметь больше одного корневого узла. Мне нужно получить корневой узел для каждого узла.

Вот что я сделал:

;WITH RCTE AS
(
    SELECT  ID, PARENT_ID, ID as ROOT_ID
    FROM TABLE r1
        WHERE NOT EXISTS (SELECT * FROM TABLE r2 WHERE r2.ID = r1.PARENT_ID)

    UNION ALL

    SELECT rh.ID, rh.PARENT_ID, 
    CASE 
        WHEN rc.ROOT_ID = rh.ID then NULL
        ELSE ROOT_ID
    END
    FROM dbo.TABLE rh
    INNER JOIN RCTE rc ON rc.ID = rh.PARENT_ID
)
select distinct ID, PARENT_ID,  
CASE WHEN ROOT_ID = ID THEN NULL
else ROOT_ID
end ROOT_ID
from RCTE

Но вот результат:

ID | PARENT_ID | ROOT_ID |
    -------------------------
    11  NULL    NULL
    60  NULL    NULL
    13  25       11
    23  11       11
    25  23       11
    13  25       60
    14  24       60
    21  60       60
    23  11       60
    24  21       60
    25  23       60

Но, как видите, у меня есть несколько дубликатов:

ID | PARENT_ID | ROOT_ID |
    -------------------------
    13  25       60
    23  11       60
    25  23       60

Заранее спасибо!


sql sql-server recursion common-table-expression recursive-query
person carlo9987    schedule 10.10.2018    source источник
comment
Вы пробовали select * from myTable where Parent_Id is null?   -  person JohnLBevan    schedule 10.10.2018
comment
Можете ли вы опубликовать некоторые образцы данных, которыми вы хотите манипулировать? Что вы пробовали? Какую версию SQL Server вы используете?   -  person Michael Bruesch    schedule 10.10.2018
comment
Рекурсивный CTE, безусловно, правильный путь.   -  person JNevill    schedule 10.10.2018
comment
Я изменил свой пост и добавил больше информации   -  person carlo9987    schedule 10.10.2018

Ответы (1)


arrow_upward
3
arrow_downward

Что-то вроде следующего должно дать вам то, что вам нужно.

 WITH recCTE AS
(
    SELECT ID, 
        Parent_ID AS original_Parent_ID, 
        Parent_ID as next_parent_id, 
        NULL as ROOT_ID,
        CASE WHEN Parent_ID IS NULL THEN 1 END AS is_root
    FROM yourtable

    UNION ALL
    SELECT
        reccte.id, 
        reccte.original_Parent_ID,
        yourtable.Parent_ID,
        CASE WHEN yourtable.Parent_ID IS NULL THEN reccte.next_parent_id ELSE reccte.ROOT_ID END,
        NULL
    FROM
        recCTE
        INNER JOIN yourtable ON reccte.next_parent_id = yourtable.ID        
)

SELECT ID, Original_Parent_ID as Parent_ID, ROOT_ID 
FROM reccte
WHERE ROOT_ID IS NOT NULL OR is_root = 1
ORDER BY ID;

В рекурсивном семени (первый SELECT) мы берем все записи и помечаем, какая из них уже является корневой.

Затем в нашем рекурсивном термине (второй SELECT) мы ищем родителя исходной записи. Если его родитель равен NULL, мы заполняем столбец ROOT_ID.

Наконец, мы ВЫБИРАЕМ из рекурсивного CTE записи с заполненным ROOT_ID или записи, которые уже являются корневой записью.

В действии:

CREATE TABLE yourtable (ID int, Parent_ID int);

INSERT INTO yourtable VALUES
  (1, NULL),
  (2, 1),
  (3, 1),
  (4, 2), 
  (5, 4),
  (6, NULL),
  (7, 6);



 WITH recCTE AS
(
    SELECT ID, 
        Parent_ID AS original_Parent_ID, 
        Parent_ID as next_parent_id, 
        NULL as ROOT_ID,
        CASE WHEN Parent_ID IS NULL THEN 1 END AS is_root
    FROM yourtable

    UNION ALL
    SELECT
        reccte.id, 
        reccte.original_Parent_ID,
        yourtable.Parent_ID,
        CASE WHEN yourtable.Parent_ID IS NULL THEN reccte.next_parent_id ELSE reccte.ROOT_ID END,
        NULL
    FROM
        recCTE
        INNER JOIN yourtable ON reccte.next_parent_id = yourtable.ID        
)

SELECT ID, Original_Parent_ID as Parent_ID, ROOT_ID 
FROM reccte
WHERE ROOT_ID IS NOT NULL OR is_root = 1
ORDER BY ID;



+----+-----------+---------+
| ID | Parent_ID | ROOT_ID |
+----+-----------+---------+
|  1 | NULL      | NULL    |
|  2 | 1         | 1       |
|  3 | 1         | 1       |
|  4 | 2         | 1       |
|  5 | 4         | 1       |
|  6 | NULL      | NULL    |
|  7 | 6         | 6       |
+----+-----------+---------+
person JNevill    schedule 10.10.2018
comment
Только что обнаружил: при наличии типа данных uniqueidentifier вместо int для столбца ID вы должны писать convert(uniqueidentifier, NULL) as ROOT_ID вместо NULL as ROOT_ID. Источник. - person Uwe Keim; 11.02.2021