[SQL] 항목의 모든 조상을 얻는다 SQL 재귀 쿼리

항목의 모든 조상을 얻는다 SQL 재귀 쿼리

ID       parent_id   name
---------------------
1        2            first 
2        4            second
3        3            third
4        5            fourth
5        -           fifth

제 조상 목록되어야 (2, 4, 5)

해결법

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

   1.

   with name_tree as (
      select id, parent_id, name
      from the_unknown_table
      where id = 1 -- this is the starting point you want in your recursion
      union all
      select c.id, c.parent_id, c.name
      from the_unknown_table c
        join name_tree p on p.parent_id = c.id  -- this is the recursion
   ) 
   select *
   from name_tree
   where id <> 1; -- exclude the starting point from the overall result
   

   SQLFiddle : http://sqlfiddle.com/#!3/87d0c/1

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

   2.이 같은 것을 사용할 수 있습니다 :

   이 같은 것을 사용할 수 있습니다 :

   with parents as 
   (
     select ID, parent_ID
     from t
     where parent_ID is not null
     union all 
     select p.ID, t.parent_ID
     from parents p
       inner join t on p.parent_ID = t.ID
         and t.parent_ID is not null
         and t.ID <> t.parent_ID
   )
   select *
     , parents = '(' + stuff
       (
         (
           select ', ' + cast(p.parent_ID as varchar(100))
           from parents p 
           where t.ID = p.ID
           for xml path('')
         ), 1, 2, ''
       ) + ')'
   from t
   order by ID
   

   데모와 SQL 바이올린.

   이 콤바인이 매우 일반적인 T-SQL 기술 - 계층 구조를 얻기 위해 CTE를 사용하고 CSV 목록을 얻기 위해 XML 경로 사용.

from https://stackoverflow.com/questions/16749095/sql-recursive-query-that-gets-all-ancestors-of-an-item by cc-by-sa and MIT license