[SQL 문제] 오라클 계층구조 쿼리의 이해

[문제]

사원번호(empno), 성명(ename), 상사의 사원번호(mgr) 를 가진 테이블 emp가 있습니다. 원본 테이블을 이용하여 계층구조 결과 테이블을 완성하는 쿼리를 작성하세요.

[원본 데이터]

[결과 데이터]

[문제 풀이]

함수를 이용한 재귀 쿼리

SELECT EMPNO
     , ENAME
     , MGR     
     , LEVEL AS LV -- LEVEL은 계층 구조에서 단계를 나타내는 함수     
     , SUBSTR(SYS_CONNECT_BY_PATH(ENAME, '-'), 2) ENAMES -- SYS_CONNECT_BY_PATH는 계층 구조에서 경로를 나타내는 함수
     , PRIOR ENAME AS MGR_ENAME -- PRIOR는 계층 구조에서 상위값을 나타내는 함수
  FROM EMP
 START WITH MGR IS NULL -- START WITH 절은 계층 구조의 시작 조건을 주는 조건절
CONNECT BY PRIOR EMPNO = MGR -- CONNECT BY 절은 계층 구조의 상하위간의 관계에 대한 조건을 주는 조건절
  ORDER SIBLINGS BY EMPNO; --  ORDER SIBLINGS BY 절은 계층 구조를 유지하면서 순서만 정렬

WITH 문을 이용한 재귀 쿼리

WITH TEMP01(EMPNO, ENAME, LV, MGR, MGR_NAME, ENAMES, EMPNOS) AS 
(
SELECT EMPNO
     , ENAME
     , 1 AS LV
     , MGR
     , '' AS MGR_ENAME
     , ENAME AS ENAMES
     , TO_CHAR(EMPNO) AS EMPNOS
  FROM EMP
 WHERE MGR IS NULL -- 최상위 데이터
 
 UNION ALL
 
SELECT T2.EMPNO
     , T2.ENAME
     , T1.LV + 1 LV
     , T2.MGR
     , T1.ENAME AS MGR_ENAME
     , T1.ENAMES || '-' || T2.ENAME AS ENAMES 
     , T1.EMPNOS || '-' || T2.EMPNO AS EMPNOS
  FROM TEMP01 T1, EMP T2
 WHERE T1.EMPNO = T2.MGR 
)
SELECT *
  FROM TEMP01
 ORDER BY EMPNOS;

[참고]

계층형 질의, WITT 문

출처 : 꿈꾸는 개발자, DBA 커뮤니티 구루비