WITH 구문 (서브쿼리를 내 마음대로)

게시일: Dec 16, 2011 5:54:34 AM

- 인라인 뷰에 별칭(alias)를 부여하고 SELECT 문장에서 사용이 가능하게 한다.

[구문 형식]

WITH <별칭1> AS ( SELECT 문장1 ),

WITH <별칭2> AS ( SELECT 문장2 ),

....

SELECT 컬럼...

FROM 별칭1, 별칭2, ... 별칭N;

WITH a AS (select 100 p_id, '홍길동' p_name from dual union all

select 200 p_id, '장길산' p_name from dual union all

select 300 p_id, '임꺽정' p_name from dual union all

select 400 p_id, '정기준' p_name from dual ) -- 인간 마스터

, b AS (select 100 p_id, 1 book_id, 1 buy_cnt from dual union all

select 200 p_id, 2 book_id, 2 buy_cnt from dual union all

select 300 p_id, 3 book_id, 1 buy_cnt from dual union all

select 400 p_id, 4 book_id, 1 buy_cnt from dual union all

select 500 p_id, 1 book_id, 5 buy_cnt from dual ) -- 인간별 책 구매내용

select b.p_id

, nvl(a.p_name, '미등록') p_name

, b.buy_cnt

from a

, b

where a.p_id(+) = b.p_id ;

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

P_ID P_NAME BUY_CNT

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

100 홍길동 1

200 장길산 2

300 임꺽정 1

400 정기준 1

500 미등록 5

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

위의 SQL은 다음과 같이 작성해도 동일한 결과를 얻을 수 있다.

WITH a AS (select 100 p_id, '홍길동' p_name from dual union all

select 200 p_id, '장길산' p_name from dual union all

select 300 p_id, '임꺽정' p_name from dual union all

select 400 p_id, '정기준' p_name from dual ) -- 인간 마스터

select b.p_id

, nvl(a.p_name, '미등록') p_name

, b.buy_cnt

from a

, (select 100 p_id, 1 book_id, 1 buy_cnt from dual union all

select 200 p_id, 2 book_id, 3 buy_cnt from dual union all

select 300 p_id, 3 book_id, 2 buy_cnt from dual union all

select 400 p_id, 4 book_id, 1 buy_cnt from dual union all

select 500 p_id, 1 book_id, 5 buy_cnt from dual ) b -- 인간별 책 구매내용

where a.p_id(+) = b.p_id

;

★★ WITH 의 진정한 특징 ★★

: 이전에 사용한 인라인 뷰를 재사용 할 수 있다.

WITH a AS (select 100 p_id, '홍길동' p_name from dual union all

select 200 p_id, '장길산' p_name from dual union all

select 300 p_id, '임꺽정' p_name from dual union all

select 400 p_id, '정기준' p_name from dual ) -- 인간 마스터

, b AS (select 100 p_id, 1 book_id, 1 buy_cnt from dual union all

select 200 p_id, 2 book_id, 3 buy_cnt from dual union all

select 300 p_id, 3 book_id, 2 buy_cnt from dual union all

select 400 p_id, 4 book_id, 1 buy_cnt from dual union all

select 500 p_id, 1 book_id, 5 buy_cnt from dual ) -- 인간별 책 구매내용

, c AS (select avg(buy_cnt) avg_cnt from b) -- 평균 구매수 (이미 사용한 쿼리(b)를 재사용 할 수 있다.)

select b.p_id

, nvl(a.p_name, '미등록') p_name

, b.buy_cnt

from a

, b

, c

where a.p_id(+) = b.p_id

and b.buy_cnt < c.avg_cnt -- 평균보다 적게 구매한 인간

;

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

P_ID P_NAME BUY_CNT

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

100 홍길동 1

300 임꺽정 2

400 정기준 1

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