Поиск родителя верхнего уровня в SQL

Ответ 1

Вы можете использовать рекурсивный 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

SQLFiddle DEMO

EDIT - для обновленного запроса родителей верхнего уровня для всех детей:

;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 - чтобы все лица немного изменились в конце:

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 DEMo

Ответ 2

Я использовал этот шаблон для связывания элементов в иерархии с корнем элемента node. Существенно рекурсивные иерархии, поддерживающие значения корня node, в качестве дополнительных столбцов, добавленных к каждой строке. Надеюсь, это поможет.

    with allRows as (
        select ItemId, ItemName, ItemId [RootId],ItemName [RootName] 
        from parentChildTable
        where ParentItemId is null
        union all
        select a1.ItemId,a1.ItemName,a2.[RootId],a2.[RootName]
        from parentChildTable a1
        join allRows a2 on a2.ItemId = a1.ParentItemId
    )   

    select * from allRows

Ответ 3

Что-то вроде этого будет работать для примера выше:

SELECT ParentId FROM RelationHierarchy 
WHERE ParentId NOT IN (SELECT CHildId FROM RelationHierarchy)

Ответ 4

Чтобы найти всех родителей верхнего уровня, используйте запрос типа:

select p.Name
from Person p
where not exists
(select null
 from RelationHierarchy r
 where r.ChildId = p.Id)

SQLFiddle здесь.

Чтобы найти родителя верхнего уровня для конкретного ребенка, используйте:

with cte as
(select t.ParentId TopParent, t.ChildId 
 from RelationHierarchy t
 left join RelationHierarchy p on p.ChildId = t.ParentId
 where p.ChildId is null
 union all
 select t.TopParent TopParent, c.ChildId 
 from cte t
 join RelationHierarchy c on t.ChildId = c.ParentId)
select p.name
from cte h
join Person p on h.TopParent = p.Id
where h.ChildId=3 /*or whichever child is required*/

SQLFiddle здесь.

Ответ 5

Попробуйте это.

Рекурсивный CTE найдет человека и поднимет иерархию, пока не найдет родителя.

-- This CTE will find the ancestors along with a measure of how far up
-- the hierarchy each ancestor is from the selected person.
with ancestor as (
  select ParentId as AncestorId, 0 as distance
  from RelationHierarchy
  where CHildId = ?

  union all

  select h.ParentId, a.distance + 1
  from ancestor a inner join RelationHierarchy rh on a.AncestorId = rh.ChildId
)
select AncestorId
from ancestor
where distance = (select max(distance) from ancestor)

Ответ 6

Единственный способ сделать это в "стандартном" SQL - это принять максимальную глубину для дерева, а затем сделать объединения для каждого уровня. Следующее получает идентификатор верхнего уровня:

select rh1.ChildId,
       coalesce(rh4.parentid, rh3.parentid, rh2.parentid, rh1.parentid) as topLevel
from RelationshipHierarchy rh1 left outer join
     RelationshipHierarchy rh2
     on rh1.parentId = rh2.childId left outer join
     RelationshipHierarchy rh3
     on rh2.parentId = rh3.childId left outer join
     RelationshipHierarchy rh4
     on rh3.parentId = rh4.childId;

Если вы хотите имя, вы можете просто присоединиться к нему в:

select rh1.ChildId,
       coalesce(rh4.parentid, rh3.parentid, rh2.parentid, rh1.parentid) as topLevel,
       p.name
from RelationshipHierarchy rh1 left outer join
     RelationshipHierarchy rh2
     on rh1.parentId = rh2.childId left outer join
     RelationshipHierarchy rh3
     on rh2.parentId = rh3.childId left outer join
     RelationshipHierarchy rh4
     on rh3.parentId = rh4.childId left outer join
     Person p
     on p.id = coalesce(rh4.parentid, rh3.parentid, rh2.parentid, rh1.parentid);

Ответ 7

Дайте этому ходу:

    select id,name
    from person p
    where not exists
    (
    select 1 
    from relationhierarchy r
    where r.childid= p.id
    )
    and exists
    (
    select 1 
    from relationhierarchy r
    where r.parentid= p.id
    )

Недостаточно просто узнать, существует ли дочерний идентификатор, как в вашем примере E, присутствует в таблице person, но не в таблице отношений иерархии.