我有两张桌子如下
表人
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列的外键
解决方法
您可以使用
recursive CTE来实现：
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
编辑 – 针对所有孩子的最高级别的父母的更新请求：
;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
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
