查找表的每一行的顶级父级[SQL Server 2008]

Question

我有以下两个表

表人

Id   Name
   1    A
   2    B
   3    C
   4    D
   5    E

表关系层次结构

ParentId   CHildId
   2         1
   3         2
   4         3

这将形成树状结构

      D
      |
      C
      |
      B
      |
      A

ParentId和ChildId是人员表的ID列的外键

我需要编写可以获取顶级父级（即每个人的根源）的SQL。

遵循CTE可以针对每个用户执行此操作。我将其转换为一个功能，并为Person的每一行运行了它。我在Person表中大约有3k行，大约需要10秒。任何人都可以建议一种可以减少花费的方法。问题是CTE运行3k次后运行的功能

DECLARE @childID INT 
SET @childID  = 1 --chield to search

;WITH RCTE AS
(
SELECT *, 1 AS Lvl FROM RelationHierarchy 
WHERE ChildID = @childID

UNION ALL

SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
 )
SELECT TOP 1 id, Name
FROM RCTE r
inner JOIN dbo.Person p ON p.id = r.ParentId
ORDER BY lvl DESC

Answer 1

我还更新了原始问题的答案，但请放心，这里也有一份副本：

;WITH RCTE AS
(
    SELECT  ParentId, ChildId, 1 AS Lvl FROM RelationHierarchy

    UNION ALL

    SELECT rh.ParentId, rc.ChildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.ChildId = rc.ParentId
)
,CTE_RN AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY r.ChildID ORDER BY r.Lvl DESC) RN
    FROM RCTE r

)
SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN CTE_RN r ON pc.id = r.CHildId AND  RN =1
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId

SQLFiddle演示

请注意，稍有不同是在CTE的递归部分中。现在，每次从锚点部分重写ChildID。此外，还添加了ROW_NUMBER（）函数（和新的CTE），以在最后获得每个孩子的顶层信息。

编辑-版本2

找到第一个查询的性能问题后，这是一个改进的版本。从上到下，而不是其他方式-消除CTE中多余的行的创建，在大量递归时应该更快得多：

;WITH RCTE AS
(
    SELECT  ParentId, CHildId, 1 AS Lvl FROM RelationHierarchy r1
    WHERE NOT EXISTS (SELECT * FROM RelationHierarchy r2 WHERE r2.CHildId = r1.ParentId)

    UNION ALL

    SELECT rc.ParentId, rh.CHildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rc.CHildId = rh.ParentId
)
SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN RCTE r ON pc.id = r.CHildId
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId

SQLFiddle演示