基于SQL Server数据表生成完整数据流转路径的解决方案咨询

问题背景 #

现有一张用于追踪数据移动的SQL表，需构建并存储所有可能的数据流转路径。尝试使用递归CTE实现时，出现了重复行（尤其在数据量较大时问题更严重），需要修正以生成符合预期的无重复结果。

现有表结构 #

期望输出 #

现有递归CTE代码 #

DECLARE @TABLE2 TABLE(
    From_Id INT,
    To_id INT
)

INSERT INTO @TABLE2 SELECT NULL,1
INSERT INTO @TABLE2 SELECT 1,2
INSERT INTO @TABLE2 SELECT NULL,3
INSERT INTO @TABLE2 SELECT 3,4
INSERT INTO @TABLE2 SELECT 4,5
INSERT INTO @TABLE2 SELECT 4,6
INSERT INTO @TABLE2 SELECT 5,7
INSERT INTO @TABLE2 SELECT 6,10
INSERT INTO @TABLE2 SELECT 2,8
INSERT INTO @TABLE2 SELECT 8,9
INSERT INTO @TABLE2 SELECT 5,9
INSERT INTO @TABLE2 SELECT 9,NULL
INSERT INTO @TABLE2 SELECT 10,NULL

;WITH Recursives AS (
        SELECT  *,
                CAST(To_id AS VARCHAR(MAX)) + ',' ID_Path
        FROM    @TABLE2
        WHERE   From_Id IS NULL
        UNION ALL
        SELECT  t.*,
        r.ID_Path + CAST(t.To_id AS VARCHAR(MAX)) + ','
        FROM    @TABLE2 t INNER JOIN
                Recursives r ON t.From_Id = r.To_id
)

SELECT  *
FROM Recursives

问题现象 #

执行上述代码后，出现重复行，例如From_Id=9, To_Id=NULL的行被多次生成，这是因为多个流转路径最终指向同一个末端节点，递归CTE会为每个路径生成一次对应行。

解决方案 #

方案1：拆分递归逻辑，单独补充末端行 #

修改递归CTE，仅处理非NULL的To_Id，最后单独将末端NULL行加入结果，从根源避免重复：

DECLARE @TABLE2 TABLE(
    From_Id INT,
    To_id INT
)

INSERT INTO @TABLE2 SELECT NULL,1
INSERT INTO @TABLE2 SELECT 1,2
INSERT INTO @TABLE2 SELECT NULL,3
INSERT INTO @TABLE2 SELECT 3,4
INSERT INTO @TABLE2 SELECT 4,5
INSERT INTO @TABLE2 SELECT 4,6
INSERT INTO @TABLE2 SELECT 5,7
INSERT INTO @TABLE2 SELECT 6,10
INSERT INTO @TABLE2 SELECT 2,8
INSERT INTO @TABLE2 SELECT 8,9
INSERT INTO @TABLE2 SELECT 5,9
INSERT INTO @TABLE2 SELECT 9,NULL
INSERT INTO @TABLE2 SELECT 10,NULL

;WITH Recursives AS (
        SELECT  *,
                CAST(To_id AS VARCHAR(MAX)) + ',' ID_Path
        FROM    @TABLE2
        WHERE   From_Id IS NULL
        UNION ALL
        SELECT  t.*,
                r.ID_Path + CAST(t.To_id AS VARCHAR(MAX)) + ','
        FROM    @TABLE2 t 
        INNER JOIN Recursives r ON t.From_Id = r.To_id
        WHERE t.To_id IS NOT NULL -- 仅递归非NULL的To_Id
)
-- 合并递归结果与末端NULL行
SELECT * FROM Recursives
UNION
SELECT t.*, NULL AS ID_Path
FROM @TABLE2 t
WHERE t.To_id IS NULL
ORDER BY From_Id, To_Id;

方案2：使用DISTINCT去重 #

若需保留所有路径信息但仅显示唯一行，可在最终查询时添加DISTINCT关键字（数据量大时效率略低）：

DECLARE @TABLE2 TABLE(
    From_Id INT,
    To_id INT
)

INSERT INTO @TABLE2 SELECT NULL,1
INSERT INTO @TABLE2 SELECT 1,2
INSERT INTO @TABLE2 SELECT NULL,3
INSERT INTO @TABLE2 SELECT 3,4
INSERT INTO @TABLE2 SELECT 4,5
INSERT INTO @TABLE2 SELECT 4,6
INSERT INTO @TABLE2 SELECT 5,7
INSERT INTO @TABLE2 SELECT 6,10
INSERT INTO @TABLE2 SELECT 2,8
INSERT INTO @TABLE2 SELECT 8,9
INSERT INTO @TABLE2 SELECT 5,9
INSERT INTO @TABLE2 SELECT 9,NULL
INSERT INTO @TABLE2 SELECT 10,NULL

;WITH Recursives AS (
        SELECT  *,
                CAST(To_id AS VARCHAR(MAX)) + ',' ID_Path
        FROM    @TABLE2
        WHERE   From_Id IS NULL
        UNION ALL
        SELECT  t.*,
                r.ID_Path + CAST(t.To_id AS VARCHAR(MAX)) + ','
        FROM    @TABLE2 t INNER JOIN
                Recursives r ON t.From_Id = r.To_id
)

SELECT DISTINCT *
FROM Recursives
ORDER BY From_Id, To_Id;

内容的提问来源于stack exchange，提问作者desperate_man