[SQL] SQL의 최상위 부모를 찾기

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는 사람 테이블의 ID 열에의 외래 키입니다

나는 톱 레벨의 상위 I-E 루트 저를 가져올 수 SQL을 작성해야합니다. 그 누구도 날이이를 도울 수있는 SQL을 제안 할 수

해결법

1. ==============================

   1.당신은 그것을 달성하기 위해 재귀 CTE를 사용할 수 있습니다 :

   당신은 그것을 달성하기 위해 재귀 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
   

   SQL 휘티 d 혀라도

   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
   

   SQL 휘티 d 혀라도

   EDIT2 - 다른 곳으로가 마지막에 조금 JOINS 모든 사람을 얻을 수 있습니다 :

   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
   

   SQL 휘티 d 혀라도

2. ==============================

   2.나는 항목의 루트 노드 계층 구조에 연관 항목이 패턴을 사용했습니다. 본질적으로 추가 열은 각 행에 추가로 루트 노드의 값을 유지 계층 재귀. 도움이 되었기를 바랍니다.

   나는 항목의 루트 노드 계층 구조에 연관 항목이 패턴을 사용했습니다. 본질적으로 추가 열은 각 행에 추가로 루트 노드의 값을 유지 계층 재귀. 도움이 되었기를 바랍니다.

       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. ==============================

   3.모든 최상위 부모를 찾으려면 같은 쿼리를 사용 :

   모든 최상위 부모를 찾으려면 같은 쿼리를 사용 :

   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.

4. ==============================

   4.이 시도.

   이 시도.

   재귀 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)
   

5. ==============================

   5.이런 식으로 뭔가 위의 예를 들어 작동합니다 :

   이런 식으로 뭔가 위의 예를 들어 작동합니다 :

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

6. ==============================

   6.당신이 "표준"SQL에서이 작업을 수행 할 수있는 유일한 방법은 트리의 최대 깊이를 가정하는 것입니다, 다음 할 각 레벨에 합류했다. 다음은 최상위 ID를 가져옵니다 :

   당신이 "표준"SQL에서이 작업을 수행 할 수있는 유일한 방법은 트리의 최대 깊이를 가정하는 것입니다, 다음 할 각 레벨에 합류했다. 다음은 최상위 ID를 가져옵니다 :

   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. ==============================

   7.경로를 사용하여 모든 상단의 부모를 가져옵니다

   경로를 사용하여 모든 상단의 부모를 가져옵니다

   select t1.path from nodes t1 inner join nodes t2
   on t1.path like t2.path+'%' 
   group by t1.path 
   having len(t1.path)-len(replace(t1.path, '/', ''))
   =min(len(t2.path)-len(replace(t2.path, '/', '')))
   

8. ==============================

   8.이것을의 이동 보내기

   이것을의 이동 보내기

       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
       )
   

   그것은 충분히 자식 ID가 귀하의 예를 들어, E와 같이 존재하는 경우 만 볼 수없는 사람이 테이블에 존재가 아니라 relationshiphierarchy 테이블에 있습니다.

from https://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql by cc-by-sa and MIT license