sql - 如何使用递归逻辑仅返回 sql 表中的根行 (SQL Server 2008 R2)

Question

这是针对 SQL Server 2008 R2 的，我是 SQL 新手，所以请尽可能具体。

Table1 内置了一些递归结构，其中 ParentId 要么是 Null(表示它是根)，要么是 ParentId 是 Table1 中另一行的 Id，表示它是子行。

示例数据集:

Table1Id    ParentId
--------------------------------------------
1           NULL
2           1
3           1
4           2
5           NULL
6           2
7           6
8           NULL
9           8

在上面的示例中，表具有以下具有 3 个根节点的树结构:

Root                    1                 5                 8
Child(teir1)        2       3                               9
Child(teir2)      4   6
Child(tier3)          7
....

有没有办法在给定任何行 ID 的情况下仅返回根行？例如:

InputId      ReturnedRowId
----------------------------
1             1
2             1
3             1
4             1
5             5
6             1
7             1
8             8
9             8

如有任何帮助，我们将不胜感激。

Answer 1

您可以使用 CTE 并遍历层次结构

    IF OBJECT_ID('tempdb..#testData') IS NOT NULL
        DROP TABLE #testData
    CREATE TABLE #testData (  
         Table1Id           INT
        ,ParentId           INT NULL
    )

    INSERT INTO #testData ( Table1Id, ParentId )
    VALUES
         (1, NULL )
        ,(2, 1 )
        ,(3, 1 )
        ,(4, 2 )
        ,(5, NULL )
        ,(6, 2 )
        ,(7, 6 )
        ,(8, NULL )
        ,(9, 8 )


    DECLARE @InputId INT

    SET @InputId = 2  --<<--Change this as appropriate

    ;WITH cteTraverse
    AS
    (
        SELECT
            T.Table1Id, T.ParentId
        FROM
            #testData T
        WHERE
            Table1Id = @InputId
        UNION ALL
        SELECT
            T1.Table1Id, T1.ParentId
        FROM
            #testData T1
        INNER JOIN
            cteTraverse T2 ON T1.Table1Id = T2.ParentId 
    )
    SELECT
        @InputId '@InputId', Table1Id 'ReturnedRowId'
    FROM
        cteTraverse
    WHERE
        ParentId IS NULL