Oracle(2022.02.24)-서브쿼리2

서브쿼리 이어서...

 

사용예) 사원테이블에서 부서별 평균급여보다 많은 급여를 받는 사원이 있는
        부서(부서번호, 부서명)를 조회하시오

<메인쿼리>

(메인쿼리:부서의 부서번호, 부서명을 조회)
   SELECT A.DEPARTMENT_ID AS 부서번호, 
          A.DEPARTMENT_NAME AS 부서명
     FROM HR.DEPARTMENTS A
    --WHERE EXISTS(서브쿼리)--1번;
    WHERE A.DEPARTMENT_ID=(서브쿼리)--2번

<메인쿼리와 1번 서브쿼리 결합>

  --1번 (EXISTS사용 오른쪽 서브쿼리 반드시 나오는데 왼쪽에는 아무것도 나오면 안된다. )
    (메인쿼리와 서브쿼리 결합 1번사용)
     SELECT A.DEPARTMENT_ID AS 부서번호, 
          A.DEPARTMENT_NAME AS 부서명
     FROM HR.DEPARTMENTS A
    WHERE EXISTS(SELECT 1  -->의미 없음 FROM절에서 WHERE절이 만족하면 1을찍고 한행이 존재하기 때문에 EXISTS가 참)
                   FROM HR.EMPLOYEES B
                  WHERE B.SALARY >( SELECT AVG(SALARY)
                                      FROM HR.EMPLOYEES C
                                     WHERE C.DEPARTMENT_ID=B.DEPARTMENT_ID)
                    AND A.DEPARTMENT_ID=B.DEPARTMENT_ID);

 

<서브쿼리 2번>

   서브쿼리의 메인쿼리:사원의 부서번호
   서브쿼리의 서브쿼리:부서별 평균급여

  --2번
    (메인쿼리:사원의 부서번호)
    SELECT B.DEPARTMENT_ID
      FROM HR.EMPLOYEES B
      WHERE B.SALARY >(서브쿼리)
    (서브쿼리:부서별 평균급여)  
    SELECT AVG(SALARY)
      FROM HR.EMPLOYEES C
     WHERE C.DEPARTMENT_ID=B.DEPARTMENT_ID
    (결합)
     SELECT B.DEPARTMENT_ID
      FROM HR.EMPLOYEES B
      WHERE B.SALARY >( SELECT AVG(SALARY)
                          FROM HR.EMPLOYEES C
                         WHERE C.DEPARTMENT_ID=B.DEPARTMENT_ID)
      ORDER BY 1;

<메인쿼리와 2번 서브쿼리 결합>

 (메인쿼리와 서브쿼리 결합 2번사용)  38명이 평균급여보다 많이 받고 있음 =연산자가 아니라 다중행이므로 IN 연산자 사용 
   SELECT A.DEPARTMENT_ID AS 부서번호, 
          A.DEPARTMENT_NAME AS 부서명
     FROM HR.DEPARTMENTS A
    WHERE A.DEPARTMENT_ID IN(SELECT B.DEPARTMENT_ID
                             FROM HR.EMPLOYEES B
                            WHERE B.SALARY >( SELECT AVG(SALARY)
                                                FROM HR.EMPLOYEES C
                                               WHERE C.DEPARTMENT_ID=B.DEPARTMENT_ID));

결과에 대한 부연설명

 <부서번호 개수 확인하기>
      10번 40번 70번은 1명밖에 없으므로 평균급여가 자신의 급여가 되는데 >이부분은 안맞으므로 10,40,70은 안나옴                                         
     SELECT DEPARTMENT_ID,COUNT(*)
       FROM HR.EMPLOYEES
      GROUP BY DEPARTMENT_ID
      ORDER BY 1;

---

사용예) 장바구니테이블에서 2005년 회원별 최고 구매수량을 기록한 회원정보를 조회하시오.
        Alias는 회원번호, 회원명, 구매수량

<메인쿼리>

  (메인쿼리: 회원정보를 조회:회원번호, 회원명, 구매수량)
    SELECT 회원번호, 회원명, 구매수량
      FROM MEMBER A, (서브쿼리) B 
      WHERE A.MEM_ID=B.회원번호;

<서브쿼리>

 (서브쿼리: 2005년 회원별 최고 구매수량)
    SELECT CART_MEMBER,
           SUM(CART_QTY)
      FROM CART 
      WHERE CART_NO LIKE '2005%'
        AND ROWNUM=1
        -->카트테이블에서 2005년도에 판매되어진 자료중에 맨 첫줄에 있는 자료만 선택하여 회원번호 순으로 집계내고 ORDER BT절하고 사실 하나가지고하니 집계낼필요도..그래서 알맞은데이터가 나오지X
      GROUP BY CART_MEMBER
      ORDER BY 2 DESC;
      
     ----------------------------------
     <수정>
    SELECT  C.CART_MEMBER AS CMID,
            C.CSUM AS CCSUM
      FROM(SELECT CART_MEMBER,
                  SUM(CART_QTY) AS CSUM
            FROM CART 
           WHERE CART_NO LIKE '2005%'
          GROUP BY CART_MEMBER
          ORDER BY 2 DESC) C
    WHERE ROWNUM=1;

수정 전

수정 후

 <결합>
    SELECT A.MEM_ID AS 회원번호,
           A.MEM_NAME AS 회원명, 
           B.CCSUM AS 구매수량
      FROM MEMBER A, (SELECT  C.CART_MEMBER AS CMID,
                             C.CSUM AS CCSUM
                       FROM(SELECT CART_MEMBER,
                                    SUM(CART_QTY) AS CSUM
                               FROM CART 
                             WHERE CART_NO LIKE '2005%'
                             GROUP BY CART_MEMBER
                             ORDER BY 2 DESC) C
                        WHERE ROWNUM=1)  B 
       WHERE A.MEM_ID=B.CMID;