在 CTE 中選擇起始節點 ID

我有下表：

create table osoba(
id int primary key,
idNd int,
nazwim nvarchar(500),
)

數據如下所示：

id idNd nazwim
1 空爺爺
2 1 個孩子1
3 1 個孩子2
4 2 孫子1child1
5 2 孫子2child1
6 3 孫子1child2
7 3 孫子 2 子 2

我有以下cte：

;WITH ret AS(
      SELECT   *
   FROM    osoba
   --WHERE   ID = 1
   UNION ALL
   SELECT o.*
   FROM    osoba o INNER JOIN
           ret r ON o.idNd = r.ID)

Select * from ret 返回每個節點和起始節點的所有後代：

id idNd nazwim
1 空爺爺
2 1 個孩子1
3 1 個孩子2
4 2 孫子1child1
5 2 孫子2child1
6 3 孫子1child2
7 3 孫子 2 子 2
6 3 孫子1child2
7 3 孫子 2 子 2
4 2 孫子1child1
5 2 孫子2child1
2 1 個孩子1
3 1 個孩子2
6 3 孫子1child2
7 3 孫子 2 子 2
4 2 孫子1child1
5 2 孫子2child1

如何檢索有關每條記錄的起始節點的資訊？我想得到這樣的結果：

idStartingNode id idNd nazwim
1 1 NULL 爺爺
1 2 1 個孩子1
1 3 1 個孩子2
1 4 2 孫子1child1
1 5 2 孫子2child1
1 6 3 孫子1child2
1 7 3 孫子2child2
3 6 3 孫子1child2
3 7 3 孫子2child2
2 4 2 孫子1child1
2 5 2 孫子2child1
2 2 1 個孩子1
3 3 1 個孩子2
6 6 3 孫子1child2
7 7 3 孫子2child2
4 4 2 孫子1child1
5 5 2 孫子2child1

在遞歸 CTE 的兩個部分中添加列。

起始節點具有IDas startingNode id。

後代的起始節點 id 是其父節點的起始節點 id：

WITH ret AS
 (
   SELECT id AS idStartingNode, o.*
   FROM    osoba o
   --WHERE   ID = 1
   UNION ALL
   SELECT r.idStartingNode, o.*
   FROM    osoba o INNER JOIN
           ret r ON o.idNd = r.ID
 ) ;

您還需要水平才能獲得有意義的報告

declare @t table (id int primary key, idNd int, nazwim varchar(50));
insert @t values 
(1, NULL, 'grandpa'),
(2, 1, 'child1'),
(3, 1, 'child2'),
(4, 2, 'grandchild1child1'),
(5, 2, 'grandchild2child1'),
(6, 3, 'grandchild1child2'),
(7, 3, 'grandchild2child2');
--select * from @t;

with cte as 
(  select id as ori, id, idNd, nazwim, 1 as level
  from @t
  union all 
  select c.ori, t.id, t.idNd, t.nazwim, level + 1
  from cte c
  join @t t
    on t.idNd = c.id
)
select * 
from cte
order by ori, level;

引用自：https://dba.stackexchange.com/questions/208353