항목의 모든 조상을 가져오는 SQL 재귀 쿼리

항목의 모든 조상을 가져오는 SQL 재귀 쿼리

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

조상 목록first그래야 한다(2, 4, 5)

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

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

다음과 같은 것을 사용할 수 있습니다.

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 Fiddle with demo.

이것은 CTE를 사용하여 계층을 얻고 FOR XML PATH를 사용하여 CSV 목록을 얻는 두 가지 매우 일반적인 T-SQL 기법을 결합합니다.

언급URL : https://stackoverflow.com/questions/16749095/sql-recursive-query-that-gets-all-ancestors-of-an-item