层次结构中的 SQL Top 父记录

Question

我有一个结构表:

公司 ID、公司名称、关联公司 ID

这样多个公司可以在一个层次结构中链接

例如

CompanyID, CompanyName, LinkedCompanyID
1          Company A,   NULL
2          Company B,   1
3          Company C,   2
4          Company D,   2
5          Company E,   4
6          Company F,   3
7          Company G,   NULL

我想要一个返回每个公司的顶级父级的查询

即

CompanyID, CompanyName, ToplevelParentID
1          Company A,   NULL (or 1 I don't mind)
2          Company B,   1
3          Company C,   1
4          Company D,   1
5          Company E,   1
6          Company F,   1
7          Company G,   NULL (or 7 I don't mind)

我看过使用递归 CTE 表达式，但我能找到的所有示例都从父级向下而不是最顶层的父级返回层次列表。

任何人都可以提供任何指示或示例查询

Answer 1

这仍然可以通过递归 CTE 实现:

CREATE TABLE #Test (CompanyID INT, CompanyName VARCHAR(20), LinkedCompanyID INT)
INSERT INTO #Test
SELECT  1, 'Company A',   NULL UNION
SELECT  2, 'Company B',   1 UNION
SELECT  3, 'Company C',   2 UNION
SELECT  4, 'Company D',   2 UNION
SELECT  5, 'Company E',   4 UNION
SELECT  6, 'Company F',   3 UNION
SELECT  7, 'Company G',   NULL

;WITH CTE AS
(   SELECT  *, 0 [Level]
    FROM    #Test
    UNION ALL
    SELECT  CTE.CompanyID, CTE.CompanyName, #Test.LinkedCompanyID, Level + 1
    FROM    CTE
            INNER JOIN #Test
                ON CTE.LinkedCompanyID = #Test.CompanyID
    WHERE   #Test.LinkedCompanyID IS NOT NULL
)

SELECT  c.CompanyID, c.CompanyName, c.LinkedCompanyID
FROM    (   SELECT  *, MAX([Level]) OVER (PARTITION BY CompanyName) [MaxLevel]
            FROM    CTE
        ) c
WHERE   MaxLevel = Level

DROP TABLE #Test