내가 보려고 정리한 오라클_서브쿼리

📌서브쿼리

✔  SQL구문안에 포함된 또 다른 SQL 구문
✔ 서브쿼리는 알려지지 않은 조건에 근거한 검색을 수행하는 경우 사용
✔ 서브쿼리는 메인쿼리의 해당 절이 실행되기 전 한번 실행됨
          --FROM절에서 제일 먼저 실행 -> WHERE절 ->SELECT절 순으로 실행
          --자기가 속한 절에서 가장 빨리 실행되어진다는 의미
✔ 서브쿼리는 '()'안에 기술하며, 연산자와 함께 사용하는 경우 연산자 우측에 위치해야 함
          --예외. INSERT문,CREATE테이블에 쓰는 서브쿼리는 괄호 없이 사용
          --이항연산자 등에서는 반드시 오른쪽에 기술
✔ 분류
  . 연관성있는 서브쿼리/연관성 없는 서브쿼리
  . 일반서브쿼리(SELECT절)/인라인서브쿼리 (FROM절)/중첩서브쿼리(WHERE절)
          --인라인 서브쿼리 매우 중요!!!!
  . 단일ORDER BY절보다 ROWNUM절이 먼저 실행됨행/다중행, 단일열/다중열

 

사용 예 1 ::
      장바구니 테이블에서 2020년 5월 /회원별 구매집계를 조회하고 /구매실적(구매금액기준)이
      많은 상위 5명의 자료를 출력하시오.
      (Alias는 회원번호,회원명,구매수량,구매금액)

❗ROWNUM

                                        ⬇ 

     ROWNUM = 가상의 컬럼 => 내가 만들지 않은 컬럼,PSUDO 

                                                          

❗메인쿼리

상위 5명의 회원번호,회원명,구매수량,구매금액 출력
서브쿼리의 뷰, MEMBER

SELECT 회원번호,회원명,구매수량,구매금액
  FROM MEMBER M,
       (서브쿼리) S
 WHERE M.MEM_ID = S.회원번호
   AND ROWNUM <= 5;
    
    
 SELECT M.MEM_ID AS 회원번호,
        M.MEM_NAME AS 회원명,
        S.구매수량,
        S.구매금액
   FROM MEMBER M,
        (서브쿼리) S
  WHERE M.MEM_ID = S.회원번호
    AND ROWNUM <= 5;

 

 

❗서브쿼리

2020년 5월 회원별 구매집계를 조회하여 내림차순으로 정렬
 (회원번호,구매수량,구매금액 : CART, PROD)

SELECT A.CART_MEMBER AS SID,
       SUM(A.CART_QTY)AS SQTY,
       SUM(A.CART_QTY*B.PROD_PRICE) AS SAMT
  FROM CART A, PROD B
 WHERE A.CART_PROD = B.PROD_ID
   AND A.CART_NO LIKE '202005%'
 GROUP BY A.CART_MEMBER
 ORDER BY 3 DESC;

         
 

❗결합

 

 SELECT M.MEM_ID AS 회원번호,
        M.MEM_NAME AS 회원명,
        S.SQTY AS 구매수량,
        S.SAMT AS 구매금액
   FROM MEMBER M,
        (SELECT A.CART_MEMBER AS SID,
                SUM(A.CART_QTY)AS SQTY,
                SUM(A.CART_QTY*B.PROD_PRICE) AS SAM
           FROM CART A, PROD
          WHERE A.CART_PROD = B.PROD_ID
            AND A.CART_NO LIKE '202005%'
          GROUP BY A.CART_MEMBER
          ORDER BY 3 DESC)S
   WHERE M.MEM_ID = S.SID
     AND ROWNUM <= 5;

 

사용예2::
      회원테이블에서 20대 여성회원 중 마일리지가 많은 3명의 회원이 2020년 4월 ~ 6월 까지 
       구매한 집계를 조회하시오

20대 여성회원 => 2020년 4월~6월 구매집계 내림차수=> 3명

 

❗메인쿼리

 20대 여성회원의 2020년 4월 ~ 6월 까지 구매한 집계

  SELECT   S.회원번호 AS ,
           M.MEM_NAME AS 회원명,
           SUM(C.CART_QTY*D.PROD_PRICE) AS 구매금액집계
    FROM   MEMBER M, CART C, PROD D,
           (서브쿼리:회원번호)S
   WHERE   S.회원번호 = M.MEM_ID
     AND   S.회원번호 = C.CART_MEMBER
     AND   C.CART_PROD = D.PROD_ID
     AND   SUBSTR(C.CART_NO,1,6) BETWEEN '202004' AND '202006'
   GROUP BY S.회원번호, M.MEM_NAME

❗서브쿼리

회원테이블에서 20대 여성회원 중 마일리지가 많은 3명의 회원의 회원번호를 출력

 

SELECT  A.MEM_ID AS MID
  FROM (SELECT MEM_ID 
          FROM MEMBER
         WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)=20
     --EXTRACT으로 나이를 구하고 TRUNC로 첫번째 자리를 자른 것(20)이 20대와 같다
           AND SUBSTR(MEM_REGNO2,1,1) IN ('2','4')
     --SUBSTR의 1번째 1자리가 2 OR 4 이면 여자
         ORDER BY MEM_MILEAGE DESC) A
 WHERE ROWNUM <=3;

❗결합                           

 SELECT S.MID AS,
        M.MEM_NAME AS 회원명,
        SUM(C.CART_QTY*D.PROD_PRICE) AS 구매금액집계
   FROM MEMBER M, CART C, PROD D,
        (SELECT A.MEM_ID AS MID
           FROM (SELECT MEM_ID 
                   FROM MEMBER
                  WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)=20 
                    AND SUBSTR(MEM_REGNO2,1,1) IN ('2','4') 
                  ORDER BY MEM_MILEAGE DESC) A
          WHERE ROWNUM <=3)S
  WHERE S.MID = M.MEM_ID
    AND S.MID = C.CART_MEMBER
    AND C.CART_PROD = D.PROD_ID
    AND SUBSTR(C.CART_NO,1,6) BETWEEN '202004' AND '202006'
  GROUP BY S.MID, M.MEM_NAME;