您可以通过以下方式实现您的目标递归CTE查找所有父记录并将它们链接到其子记录。
虚拟表设置:
CREATE TABLE #Table1
(
[Row no] INT ,
[Col 1] INT ,
[Col 2] VARCHAR(1) ,
[Col 3] INT ,
[Col 4] INT
);
INSERT INTO #Table1
( [Row no], [Col 1], [Col 2], [Col 3], [Col 4] )
VALUES ( 1, 1, 'X', 1, 5 ),
( 2, 2, 'Y', 1, 6 ),
( 3, 5, 'Z', 2, 7 ),
( 4, 6, 'T', 2, 0 ),
( 5, 7, 'T', 3, 0 ),
( 6, 6, 'W', 2, 0 );
递归 CTE:
;WITH cte
AS ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY t1.[Col 1] ) GroupNo
FROM #Table1 t1
WHERE t1.[Col 1] NOT IN ( SELECT [Col 4] FROM #Table1 )
UNION ALL
SELECT t.* ,
cte.GroupNo
FROM #Table1 t
INNER JOIN cte ON cte.[Col 4] = t.[Col 1]
)
SELECT *
FROM cte
ORDER BY cte.GroupNo , cte.[Row no]
DROP TABLE #Table1
这将 2 个查询与UNION ALL
。第一个查询查找顶级项目,其中的值[Col 1]
没有出现在[Col 4]
:
WHERE t1.[Col 1] NOT IN ( SELECT [Col 4] FROM #Table1 )
第二个查询使用此查找第一个查询的子记录JOIN
:
INNER JOIN cte ON cte.[Col 4] = t.[Col 1]
对于排序,我使用以下命令给出第一个查询的结果GroupNo
,稍后用于对记录进行排序:
ROW_NUMBER() OVER ( ORDER BY t1.[Col 1] ) GroupNo