sql - CTE获取每个父母的所有孩子和嵌套孩子

Question

我陷入了一个不断在脑海中寻找解决方案的问题。

我有一个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。

标签： sqlsql-servertsqlcommon-table-expressionsql-server-2019

Answer 1

在 cte 的第一部分中，选择所有以 ownid 作为根 id 的行。然后在第二部分（全部联合之后）选择 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)

输出：

父母身份	用户身份
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<小提琴在这里