A CONNECT BY LEVEL 여기서 A는 무시해도 될꺼 같음.
WITH t ( sd, dt, cd, lv ) AS
(
SELECT '2014-10-11', '2014-10-12', 'BGO', 5 FROM DUAL UNION ALL
SELECT '2014-10-12', '2014-10-14', 'BGA', 1 FROM DUAL UNION ALL
SELECT '2014-10-14', '2014-10-15', 'BGB', 1 FROM DUAL UNION ALL
SELECT '2014-10-18', '2014-10-19', 'BGC', 1 FROM DUAL UNION ALL
SELECT '2014-10-18', '2014-10-19', 'BGD', 1 FROM DUAL UNION ALL
SELECT '2014-10-18', '2014-10-19', 'BGE', 1 FROM DUAL UNION ALL
SELECT '2014-10-18', '2014-10-19', 'BGF', 1 FROM DUAL
)
////이렇게 데 T라는 가상의 데이터 테이블 T를 만들어 넣고... 순서대로 보자...
SELECT A.*,
T.CD,
T.lv
FROM
(SELECT TO_CHAR(TO_DATE(SD,'YYYY-MM-DD') + (LEVEL-1),'YYYY-MM-DD') SD
FROM
(SELECT MIN(SD) SD
FROM T //2번 최소 날짜를 구함.
) A CONNECT BY LEVEL <=
(SELECT TO_DATE(MAX(DT),'YYYY-MM-DD')+1 - TO_DATE(MIN(SD),'YYYY-MM-DD')
FROM T //1번 DT의 후속날자최대값에서 SD의 최소날짜값을빼면 9일이 나옴..
)
) A //3번 최소날짜로부터 LEVEL일수만큼 더해진 날을 구함 LEVEL은 1부터 시작하므로 9까지 총 9번 연산되어 11일부터 19일까지 구해짐.
LEFT OUTER JOIN T
ON A.SD BETWEEN T.SD AND T.DT
WHERE T.CD IS NOT NULL; //4번 레프트 아웃트 조인으로 인하여 위에 구하여진 날짜값을 기준으로 그날짜에 포함되어진 값을 구하는데 T.CD 가 널이 아닌경우에만 구함.
추가로 CONNECT BY LEVEL 에 대해서 검색내용을 추가함.
Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보자
CONNECT_BY_ROOT
계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,CONNECT_BY_ROOT empno "Root empno", level FROM emp START WITH job='PRESIDENT'CONNECT BY PRIOR empno=mgr;ENAME EMPNO Root empno LEVEL------------------ ------- ----------- -------KING 7839 7839 1 JONES 7566 7839 2 SCOTT 7788 7839 3 ADAMS 7876 7839 4 FORD 7902 7839 3 SMITH 7369 7839 4 |
CONNECT_BY_ISLEAF
계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하이 레벨이면 1, 아니면 0
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, CONNECT_BY_ISLEAF "leaf", level FROM emp START WITH job='PRESIDENT'CONNECT BY NOCYCLE PRIOR empno=mgr;ENAME EMPNO leaf LEVEL-------------------- ---------- ---------- ----------KING 7839 0 1 JONES 7566 0 2 SCOTT 7788 0 3 ADAMS 7876 1 4 FORD 7902 1 3 BLAKE 7698 0 2 MARTIN 7654 1 3 TURNER 7844 1 3 JAMES 7900 1 3 CLARK 7782 0 2 MILLER 7934 1 3 |
SYS_CONNECT_BY_PATH
계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -- SQL*Plus에서만 깔끔하게 보기위해서 COL PATH FORMAT A40 -- SYS_CONNECT_BY_PATH 예제SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, SYS_CONNECT_BY_PATH(ename, '/') "PATH" FROM emp START WITH job='PRESIDENT'CONNECT BY PRIOR empno=mgr;ENAME EMPNO PATH-------------------- ---------- ---------------------------KING 7839 /KING JONES 7566 /KING/JONES SCOTT 7788 /KING/JONES/SCOTT ADAMS 7876 /KING/JONES/SCOTT/ADAMS FORD 7902 /KING/JONES/FORD BLAKE 7698 /KING/BLAKE MARTIN 7654 /KING/BLAKE/MARTIN TURNER 7844 /KING/BLAKE/TURNER JAMES 7900 /KING/BLAKE/JAMES CLARK 7782 /KING/CLARK MILLER 7934 /KING/CLARK/MILLER |
아래와 같이 Leaf Node만 전체 PATH 정보가 나오도록 작성 할 수 있다
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- SQL*Plus에서만 깔끔하게 보기위해서 COL PATH FORMAT A40 SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path FROM emp WHERE CONNECT_BY_ISLEAF = 1 START WITH mgr IS NULLCONNECT BY PRIOR empno = mgr; LEVEL PATH--------- ------------------------- 4 KING,JONES,SCOTT,ADAMS 3 KING,JONES,FORD 3 KING,BLAKE,MARTIN 3 KING,BLAKE,TURNER 3 KING,BLAKE,JAMES 3 KING,CLARK,MILLER |
ORDER SIBLINGS BY
계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- SQL*Plus에서만 깔끔하게 보기위해서 COL ename FORMAT A25 COL ename2 FORMAT A10-- ORDER SIBLINGS BY 예-- 정렬이 정상적으로 수행된 것을 확인 할 수 있다.SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, ename ename2, empno, level FROM emp START WITH job='PRESIDENT'CONNECT BY NOCYCLE PRIOR empno=mgr ORDER SIBLINGS BY ename2;ENAME ENAME2 EMPNO LEVEL-------------------- ---------- ---------- ----------KING KING 7839 1 BLAKE BLAKE 7698 2 JAMES JAMES 7900 3 MARTIN MARTIN 7654 3 TURNER TURNER 7844 3 CLARK CLARK 7782 2 MILLER MILLER 7934 3 JONES JONES 7566 2 FORD FORD 7902 3 SCOTT SCOTT 7788 3 ADAMS ADAMS 7876 4-- ORDER BY 예-- 정렬이 이상하게 수행된 것을 확인 할 수 있다.SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, ename ename2, empno, level FROM emp START WITH job='PRESIDENT'CONNECT BY NOCYCLE PRIOR empno=mgr ORDER BY ename2;ENAME ENAME2 EMPNO LEVEL-------------------- ---------- ---------- ---------- ADAMS ADAMS 7876 4 BLAKE BLAKE 7698 2 CLARK CLARK 7782 2 FORD FORD 7902 3 JAMES JAMES 7900 3 JONES JONES 7566 2KING KING 7839 1 MARTIN MARTIN 7654 3 MILLER MILLER 7934 3 SCOTT SCOTT 7788 3 TURNER TURNER 7844 3 |
태그
- CONNECT_BY_ROOT SYS_CONNECT_BY_PATH CONNECT_BY_ISLEAF CONNECT BY START WITH PRIOR ORDER SIBLINGS BY계층구조쿼리
문서에 대하여
- - 강좌 URL : http://www.gurubee.net/lecture/1903
- - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
'Oracle' 카테고리의 다른 글
| 순위함수 RANK DENSE_RANK ROW_NUMBER 특징별 기억하면 좋고 (0) | 2014.12.03 |
|---|---|
| 집계함수와 분석함수 (0) | 2014.12.03 |
| 세로로된 데이터 가로로 출력하기. (0) | 2014.12.02 |
| 오라클 트리거 트리거란 무엇인가? (0) | 2014.12.02 |
| 오라클 기본 문접 ㅎㅎ (0) | 2014.12.02 |
은호아빠
여행, 맛집, 일상, 프로그래밍, 개발자, 윈도우, 웹, jsp, spring, db, mysql, oracle, c#