我有一个表格如下:
身份证 |儿童身份证 |名字 ----------------------- 1 空名称12 1 名称23 2 名称34 3 名称45 4 名称56 5 名称67 6 名称78 7 名称89 8 名称910 9 名称1011 3 姓名11
现在我想要一个查询,它可以为至少有一个子级的每个记录获取所有可能的子项,并返回如下所示的数据集:
身份证 |家长编号 | 姓名 | 家长编号 ----------------------------------------- 1 空名称1 11,10,9,8,7,6,5,4,3,22 1 姓名2 11,10,9,8,7,6,5,4,33 2 姓名3 11,10,9,8,7,6,5,44 3 姓名4 10,9,8,7,6,55 4 姓名5 10,9,8,7,66 5 姓名6 10,9,8,77 6 姓名7 10,9,88 7 姓名8 10,99 8 名称9 10
使用此查询。
更新结果: 小提琴手演示
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
你可以尝试使用这个:
SELECT a.*,x.ParentIDs
FROM YourTable a
CROSS APPLY (SELECT STUFF(CAST((SELECT ',' + CONVERT(VARCHAR,b.ID)
FROM YourTable b WHERE b.ID > a.ID AND ISNULL(b.ParentID,0) > ISNULL(a.ParentID,0) ORDER BY ID DESC
FOR XML PATH(''), TYPE) AS VARCHAR(MAX)),1,1,'') AS ParentIDs ) x
WHERE x.ParentIDs IS NOT NULL