Enjoy Development

지식관리, 메모장, 기억저장소, 참고자료, ...

Oracle 오라클 connect by를 이용한 계층 쿼리 샘플

2014.08.20 08:54 | 조회 수 : 29832

   

/*******************************************************************************

* 오라클 connect by 구문을 이용한 계층 쿼리 샘플

   

* connect by 구문 실행 순서

1) start with : 계층 구조의 ROOT 조건

2) connect by : 계층 구조를 연결 할 조건

3) where : 결과로 표시 할 조건

   

* connect by "PRIOR" 키워드

- prior 사전적 의미 : 사전의, (~보다) 우선하는, ~전의 (출처:네이버 사전)

- 계층 구조를 따라가기 위해 시작하는 ROW의 컬럼 쪽에 입력한다.

Ex) TOP-BOTTOM 구조로 따라가는 경우 TOP ROW의 컬럼에 입력

Ex) BOTTOM-TOP 구조로 따라가는 경우 BOTTOM ROW의 컬럼에 입력

   

* 샘플 데이터

-----------------------------------

No Class PCode CCode Name

-----------------------------------

1 M1 A B1 A-B1

2 M1 A B2 A-B2

3 M1 B1 C1 A-B1-C1

4 M1 B1 C2 A-B1-C2

5 M1 B2 C3 A-B2-C3

6 M1 B2 C4 A-B2-C4

7 M1 B2 C5 A-B2-C5

   

8 M2 A B2 A-B2

9 M2 A B3 A-B3

10 M2 B2 C1 A-B2-C1

11 M2 B2 C2 A-B2-C2

12 M2 B2 C3 A-B2-C3

13 M1 B3 C4 A-B3-C4

14 M1 B3 C5 A-B3-C5

*******************************************************************************/

WITH

WITH_T AS (

SELECT 1 AS NO, 'M1' AS CLASS, 'A' AS PCODE, 'B1' AS CCODE, 'A-B1' AS NAME FROM DUAL

UNION ALL SELECT 2 AS NO, 'M1' AS CLASS, 'A' AS PCODE, 'B2' AS CCODE, 'A-B2' AS NAME FROM DUAL

UNION ALL SELECT 3 AS NO, 'M1' AS CLASS, 'B1' AS PCODE, 'C1' AS CCODE, 'A-B1-C1' AS NAME FROM DUAL

UNION ALL SELECT 4 AS NO, 'M1' AS CLASS, 'B1' AS PCODE, 'C2' AS CCODE, 'A-B1-C2' AS NAME FROM DUAL

UNION ALL SELECT 5 AS NO, 'M1' AS CLASS, 'B2' AS PCODE, 'C3' AS CCODE, 'A-B2-C3' AS NAME FROM DUAL

UNION ALL SELECT 6 AS NO, 'M1' AS CLASS, 'B2' AS PCODE, 'C4' AS CCODE, 'A-B2-C4' AS NAME FROM DUAL

UNION ALL SELECT 7 AS NO, 'M1' AS CLASS, 'B2' AS PCODE, 'C5' AS CCODE, 'A-B2-C5' AS NAME FROM DUAL

   

UNION ALL SELECT 8 AS NO, 'M2' AS CLASS, 'A' AS PCODE, 'B2' AS CCODE, 'A-B2' AS NAME FROM DUAL

UNION ALL SELECT 9 AS NO, 'M2' AS CLASS, 'A' AS PCODE, 'B3' AS CCODE, 'A-B3' AS NAME FROM DUAL

UNION ALL SELECT 10 AS NO, 'M2' AS CLASS, 'B2' AS PCODE, 'C1' AS CCODE, 'A-B2-C1' AS NAME FROM DUAL

UNION ALL SELECT 11 AS NO, 'M2' AS CLASS, 'B2' AS PCODE, 'C2' AS CCODE, 'A-B2-C2' AS NAME FROM DUAL

UNION ALL SELECT 12 AS NO, 'M2' AS CLASS, 'B2' AS PCODE, 'C3' AS CCODE, 'A-B2-C3' AS NAME FROM DUAL

UNION ALL SELECT 12 AS NO, 'M2' AS CLASS, 'B3' AS PCODE, 'C4' AS CCODE, 'A-B3-C4' AS NAME FROM DUAL

UNION ALL SELECT 12 AS NO, 'M2' AS CLASS, 'B3' AS PCODE, 'C5' AS CCODE, 'A-B3-C5' AS NAME FROM DUAL

)

SELECT LPAD('.', LEVEL) || LEVEL AS LVL, T.*

FROM WITH_T T

WHERE 1=1

START WITH CLASS = 'M2'

AND PCODE = 'A'

CONNECT BY PRIOR CCODE = PCODE

AND PRIOR CLASS = CLASS

ORDER BY NO;

Powered by EnjoyDev | Xpress Engine | DNS Powered by DNSEver.com