[Oracle 공부하기]계층형 쿼리

계층형 쿼리

데이터 조회할 때 상위와 하위 관계를 표시하는데 사용하는 함수

함수명 : CONNECT BY

ex) 

1) SELECT LEVEL, LPAD(EMPNO, LEVEL*4,'*') AS EMPNO1, ENAME, MGR

FROM EMP

START WITH NGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;

2) SELECT LEVEL, LPAD(ENAME, LEVEL*4, '*') AS ENAME1

, SUBSTR(SYS_CONNECT_BY_PATH(ENAME,'-'),2) ENAME2, MGR

 FROM EMP

 START WITH MGR IS NULL

 CONNECT BY PRIOR EMPNO = MGR;

3) 레벨별로 급여 합계와 사원수를 조회하는 경우

 SELECT LEVEL, SUM(SAL) TOTAL, COUNT(EMPNO) CNT

 FROM EMP

 START WITH MGR IS NULL

 CONNECT BY PRIOR EMPNO = MGR

 GROUP BY LEVEL

 ORDER BY LEVEL