[Oracle / 오라클] Sub Query 서브쿼리 예제

[예제] 포인트 테이블에서 고객코드가 '2019000' 이후인 고객 중 포인트 합이 10,000 보다 큰 여성을 대상으로 고객코드, 고객명, 포인트 합계를 검색 

- 출처 : 하루10분 _SQL

1) 포인트 테이블에서 고객코드가 '2019000' 이후인 고객 중 포인트 합

SELECT S1.CUSTOMER_CD,
S1.TOT_POINT
FROM (SELECT CUSTOMER_CD, SUM(POINT) TOT_POINT 
	FROM TB_POINT WHERE CUSTOMER_CD >= '2019000' 
    GROUP BY CUSTOMER_CD) S1
WHERE S1.TOT_POINT >10000;

2) 여성을 대상으로 조건 추가

SELECT S2.* 
    FROM(
       SELECT S1.CUSTOMER_CD,
              S1.TOT_POINT
        FROM (
            SELECT CUSTOMER_CD, SUM(POINT) TOT_POINT 
            FROM TB_POINT WHERE CUSTOMER_CD >= '2019000' 
            GROUP BY CUSTOMER_CD
            ) S1
        WHERE S1.TOT_POINT >10000) S2 
WHERE S2.CUSTOMER_CD IN 
	(SELECT CUSTOMER_CD FROM TB_CUSTOMER WHERE MW_FLG = 'W');

3) 고객명, 포인트 합계 조회 

 

2) SELECT *
FROM(
       SELECT S1.CUSTOMER_CD,
              S1.TOT_POINT
        FROM (
            SELECT CUSTOMER_CD, SUM(POINT) TOT_POINT 
            FROM TB_POINT WHERE CUSTOMER_CD >= '2019000' 
            GROUP BY CUSTOMER_CD
            ) S1
        WHERE S1.TOT_POINT > 10000) S2 
WHERE S2.CUSTOMER_CD IN (SELECT CUSTOMER_CD FROM TB_CUSTOMER WHERE MW_FLG='w');

 

 

3) 고객명, 포인트 합계를 검색 - 최종 출력 항목 추가 

SELECT S2.CUSTOMER_CD,
    (SELECT CT.CUSTOMER_NM
    FROM TB_CUSTOMER CT
    WHERE CT.CUSTOMER_CD = S2.CUSTOMER_CD) CUSTOMER_NM, S2.TOT_POINT --고객명, 포인트합
FROM(
       SELECT S1.CUSTOMER_CD,
              S1.TOT_POINT
        FROM (
            SELECT CUSTOMER_CD, SUM(POINT) TOT_POINT 
            FROM TB_POINT WHERE CUSTOMER_CD >= '2019000' 
            GROUP BY CUSTOMER_CD
            ) S1
        WHERE S1.TOT_POINT >10000) S2 
WHERE S2.CUSTOMER_CD IN 
    (SELECT CUSTOMER_CD FROM TB_CUSTOMER WHERE MW_FLG='w');