게시일: 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 절에서 조인 이외의 조건)을 처리한다.
[참고] 계층형 쿼리의 처리 절차