sql - 如何获取自引用表的每条记录的所有子项

Question

我有一个表格如下:

ID   | ChildsID | Name         -----------------------              1       NULL       Name12       1          Name23       2          Name34       3          Name45       4          Name56       5          Name67       6          Name78       7          Name89       8          Name910      9          Name1011      3          Name11

现在我想要一个查询，它可以为至少有一个子项的每个记录获取所有可能的子项，并返回一个像这样的数据集:

ID   | ParentID |   Name   |   ParentIDs         -----------------------------------------              1       NULL       Name1       11,10,9,8,7,6,5,4,3,22       1          Name2       11,10,9,8,7,6,5,4,33       2          Name3       11,10,9,8,7,6,5,44       3          Name4       10,9,8,7,6,55       4          Name5       10,9,8,7,66       5          Name6       10,9,8,77       6          Name7       10,9,88       7          Name8       10,99       8          Name9       10

Answer 1

使用此查询。

更新结果: Fiddler Demo

    CREATE TABLE TABLE1 (ID INT, ParentID INT, NAME VARCHAR(10));
INSERT INTO TABLE1 VALUES(1, NULL, 'Name1');
INSERT INTO TABLE1 VALUES(2, 1, 'Name2');
INSERT INTO TABLE1 VALUES(3, 2, 'Name3');
INSERT INTO TABLE1 VALUES(4, 3, 'Name4');
INSERT INTO TABLE1 VALUES(5, 4, 'Name5');
INSERT INTO TABLE1 VALUES(6, 5, 'Name6');
INSERT INTO TABLE1 VALUES(7, 6, 'Name7');
INSERT INTO TABLE1 VALUES(8, 7, 'Name8');
INSERT INTO TABLE1 VALUES(9, 8, 'Name9');
INSERT INTO TABLE1 VALUES(10, 9, 'Name10');
INSERT INTO TABLE1 VALUES(11, 3, 'Name11');

  SELECT ID, ParentID , Name, Child = STUFF((
          SELECT ',' + CAST(B.ID AS VARCHAR(100)) FROm TABLE1 AS B 
          WHERE ISNULL(A.ID, 0) < B.ID AND B.ID NOT IN(
              ISNULL((SELECT TOP 1 C.ID FROm TABLE1 AS C 
                      WHERE C.ParentID IN (
                            SELECT ParentID FROM TABLE1 WHERE ID <= A.ID) 
                      ORDER BY C.ID DESC), 0))  
          ORDER BY B.ID DESC 
          FOR XML PATH (''), type).value('.', 'varchar(max)'), 1,1,'')
 FROm TABLE1 AS A

旧结果:

 SELECT ID, ParentID , Name, ParentIDs = STUFF((
  SELECT ',' + CAST(B.ID AS VARCHAR(100)) FROm MyTable AS B 
  WHERE ISNULL(A.ID, 0) < B.ID ORDER BY B.ID  DESC FOR XML PATH (''), type).value('.',
          'varchar(max)'), 1,1,'')
 FROm MyTable AS A