CTE для получения всех дочерних и вложенных дочерних элементов каждого родителя

Я застрял в проблеме, которая постоянно крутится в моей голове, чтобы найти решение.

У меня есть таблица users с 6k строк, и они связаны как parent-child. Проблема в том, что мне нужны результаты не только на одном уровне отношений родитель-потомок, но и на всех уровнях (до последнего потомка) для каждого userid.

У меня есть эти данные в моей таблице users.

UserID | ParentID
1        NULL
2        1
3        1
4        2
5        2
6        5
7        6
8        6
9        NULL

Я хочу, чтобы эти данные были выводом CTE.

ParentID  |  UserID
1            1
1            2
1            3
1            4
1            5
1            6
1            7
1            8
2            2
2            4
2            5
2            6
2            7
2            8
3            3
4            4
5            5
5            6
5            7
5            8
6            6
6            7
6            8
7            7
8            8
9            9

Примечание. ParentID включает в себя не только дочерние элементы, но также дочерние элементы своих дочерних элементов и самого себя. Я использую MSSQL 2019.


sql sql-server tsql sql-server-2019 common-table-expression
person zulqadar idrishi    schedule 08.04.2021    source источник

Ответы (1)


arrow_upward
2
arrow_downward

В первой части cte выберите все строки с ownid в качестве корневого идентификатора. Затем во второй части (после объединения всех) выберите parentid как rootid.

Операторы схемы и вставки:

 create table users (UserID int,  ParentID int);
 insert into users values (1,        NULL);
 insert into users values (2,        1);
 insert into users values (3,        1);
 insert into users values (4,        2);
 insert into users values (5,        2);
 insert into users values (6,        5);
 insert into users values (7,        6);
 insert into users values (8,        6);
 insert into users values (9,        NULL);

Запрос:

 with cte as
 (
   select userid rootid, userid, parentid from users 
   union all
   select cte.rootid rootid, users.userid, users.parentid from users
   inner join cte on users.parentid=cte.userid
 )
 select rootid parentid,userid from cte
 order by rootid ,userid
 option (maxrecursion 0)

Выход:

parentid userid
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
2 2
2 4
2 5
2 6
2 7
2 8
3 3
4 4
5 5
5 6
5 7
5 8
6 6
6 7
6 8
7 7
8 8
9 9

db‹fiddle здесь

person Kazi Mohammad Ali Nur    schedule 08.04.2021