在SQL中查找顶级父级

表人

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是Person Table的Id列的外键

我需要编写可以抓取我顶级父级i-e根的SQL.任何人都可以建议任何可以帮助我实现这一点的SQL

解决方法

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

SQLFiddle DEMO

编辑 – 针对所有孩子的最高级别的父母的更新请求：

;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 r.ChildId,pc.Name AS ChildName,r.ParentId,pp.Name AS ParentName
FROM CTE_RN r
INNER JOIN dbo.Person pp ON pp.id = r.ParentId
INNER JOIN dbo.Person pc ON pc.id = r.ChildId
WHERE RN =1

SQLFiddle DEMO

EDIT2 – 让所有的人改变JOINS一点点到底：

SELECT pc.Id AS ChildID,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 DEMo

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!