계층형 쿼리

게시일: Nov 01, 2011 12:5:4 AM

-- 테이블 구성 내용

select a.*

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

) a ;

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

PRODUCT_ID PARENT_ID PRODUCT_NAME

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

1 컴퓨터

2 1 본체 --> 컴퓨터

3 1 모니터 --> 컴퓨터

4 1 프린터 --> 컴퓨터

5 2 Mother Board --> 본체

6 2 랜카드 --> 본체

7 2 Power Supply --> 본체

8 5 RAM --> Mother Board

9 5 CPU --> Mother Board

10 5 그래픽 장치 --> Mother Board

11 5 기타 장치 --> Mother Board

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

-- 조인을 활용한 계층형 쿼리

select a.product_name

, a.product_id

, b.product_name parent_product

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

) a

, (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

) b

where b.product_id(+) = a.parent_id

order by a.product_id

;

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

PRODUCT_NAME PRODUCT_ID PARENT_PRODUCT

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

컴퓨터 1

본체 2 컴퓨터

모니터 3 컴퓨터

프린터 4 컴퓨터

Mother Board 5 본체 --> parent_product에 '본체'라고는 나와있지만 프린터 밑에 있어서 프린터 하위항목으로 착각할 가능성이 있음.

랜카드 6 본체

Power Supply 7 본체

RAM 8 Mother Board

CPU 9 Mother Board

그래픽 장치 10 Mother Board

기타 장치 11 Mother Board

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

-- START WITH ... CONNECT BY 절을 사용한 계층형 쿼리 (8i부터 지원)

select level -- 계층형 구조에서의 각 레벨을 의미함 (루트노드가 레벨 1, 계층형 쿼리에서만 사용)

, product_name

, lpad(' ', 4*(level-1)) || product_name

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

)

start with parent_id is null -- 루트노드 검색

connect by prior product_id = parent_id -- 부모와 자식노드를 연결

--connect by parent_id = prior product_id -- 동일한 결과

--connect by product_id = prior parent_id -- 결과가 다름 (** 주의 **)

;

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

LEVEL PRODUCT_NAME PRODUCT_NAME

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

1 컴퓨터 컴퓨터

2 본체 본체

3 Mother Board Mother Board

4 RAM RAM

4 CPU CPU

4 그래픽 장치 그래픽 장치

4 기타 장치 기타 장치

3 랜카드 랜카드

3 Power Supply Power Supply

2 모니터 모니터

2 프린터 프린터

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

☞ ORDER BY를 사용하지 않더라고 계층과 레벨에 따라 스스로 알아서 정렬한다.

☞ 오라클이 계층형 쿼리를 처리하는 순서

① 조인이 사용되었다면 가장 먼저 조인을 처리한다.

② 그 다음으로 CONNECT BY 조건을 처리한다.

③ 마지막으로 나먼지 조건(WHERE 절에서 조인 이외의 조건)을 처리한다.

[참고] 계층형 쿼리의 처리 절차