skymong9.egloos.com

Activation...

포토로그



[MSSQL] 재귀적 CTE 쿼리 my dev_study

재귀적 cte 를 통한 쿼리


샘플은 tbldept

WITH deptCTE(deptkey,parentkey,deptname, level,priority)
AS
(
  SELECT deptkey,parentkey,deptname , 0  ,priority
       FROM tbldept
       WHERE parentkey = 0
  UNION ALL
  SELECT AA.deptkey,aa.parentkey,aa.deptname, BB.level+1,aa.priority
   FROM tbldept AS AA INNER JOIN deptCTE AS BB ON AA.parentkey = BB.deptkey  and aa.parentkey not in ( 0,-1)
)
SELECT deptkey,parentkey,replicate(' -', level)+deptname as deptname,level FROM deptCTE ORDER BY  priority,level

 


트리구조 표현한 쿼리네요..

참고)

cte 는 ansi-sql99 표준 ...
보통의 dbms 는 ansi-sql99 는 호환..^^


덧글

댓글 입력 영역