[Firebird] 계층적 데이터 쿼리

반응형

with recursive CTE
    --(PATH_, Level, PRJ_ID, ID, HR_ID, NAME)
as
(
   select CAST(NAME AS VARCHAR(3000)) AS PATH_, 1 as Level, PRJ_ID, ID, HR_ID, NAME
   from CteTable 

   where PRJ_ID = 'A1607001'
    --and ID = 'C00002'
    and NAME = '연습'

   union all

   select cast((t.PATH_ || '/' || e.NAME) as VARCHAR(3000)) as PATH_,(t.Level + 1) as Level, e.PRJ_ID, e.ID, e.HR_ID, e.NAME
   from CteTable e join CTE t on e.HR_ID = t.ID
)
select *
from CTE

출처: https://devdb.tistory.com/25 [DB 관련:티스토리]