Oracle :: 서브 상관 쿼리

■ 서브 상관 쿼리

- 메인 쿼리가 있는 테이블의 컬럼이 서브 쿼리의 조건절(WHERE, HAVING)에 사용되는 경우!가 서브 상관 쿼리다~!

SELECT ENAME"사원명", SAL"급여", 1 "급여등수"
FROM EMP;

SELECT ENAME"사원명", SAL"급여", (1) "급여등수" -- 괄호 넣어도 오류나지 않음 동일한 구문임
FROM EMP;

-- 위 코드들은 급여등수가 고정된 값만 나옴. 이를 서브 상관 쿼리를 활용해서 수정해보자
SELECT ENAME"사원명", SAL"급여", (SELECT COUNT(*) +1
                                 FROM EMP
                                 WHERE SAL > 1600) "급여등수"  -- 아직까지 고정 값 
FROM EMP;


SELECT E.ENAME"사원명", E.SAL"급여", (SELECT COUNT(*) +1
                                     FROM EMP
                                     WHERE SAL > E.SAL) "급여등수"
FROM EMP E
ORDER BY 3;
--==>>
/*
KING	5000	 1
FORD	3000	 2
SCOTT	3000	 2
JONES	2975	 4
BLAKE	2850	 5
CLARK	2450	 6
ALLEN	1600	 7
TURNER	1500	 8
MILLER	1300	 9
WARD	1250	10
MARTIN	1250	10
ADAMS	1100	12
JAMES	 950	13
SMITH	 800	14
*/

 

■ 서브 상관 쿼리 문제 풀이

--○ EMP 테이블을 대상으로
--   사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.
--   단, RANK() 함수를 사용하지 않고, 서브상관쿼리를 활용할 수 있도록 한다.

SELECT ENAME"사원명", SAL"급여", DEPTNO"부서번호"
     , (SELECT COUNT(*) +1
        FROM EMP
        WHERE SAL > E.SAL
          AND DEPTNO = E.DEPTNO) "부서내급여등수"
     , (SELECT COUNT(*)+1
        FROM EMP
        WHERE SAL < E.SAL) "전체급여등수"
FROM EMP E
ORDER BY 3,4;
--==>>
/*
KING	5000	10	1	14
CLARK	2450	10	2	9
MILLER	1300	10	3	6
SCOTT	3000	20	1	12
FORD	3000	20	1	12
JONES	2975	20	3	11
ADAMS	1100	20	4	3
SMITH	 800	20	5	1
BLAKE	2850	30	1	10
ALLEN	1600	30	2	8
TURNER	1500	30	3	7
MARTIN	1250	30	4	4
WARD	1250	30	4	4
JAMES	 950	30	6	2
*/


--○ EMP 테이블을 대상으로 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
                                            - 각 부서 내에서 입사일자별로 누적된 급여의 합
----------------------------------------------------------------------
사원명     부서번호      입사일         급여    부서내입사별급여누적
--------------------------------------------------------------------
SMITH       20         1980-12-17      800         800
JONES	    20         1981-04-02	  2975	      3775
FORD	    20         1981-12-03     3000        6775
                     :
--------------------------------------------------------------------                      
*/

SELECT E.ENAME"사원명", E.DEPTNO"부서번호", E.HIREDATE"입사일", E.SAL"급여"
     , (SELECT SUM(SAL)
        FROM EMP
        WHERE DEPTNO = E.DEPTNO
          AND HIREDATE >=E.HIREDATE) "부서내입사별급여누적"
FROM EMP E
ORDER BY 2,3;

--같이 푼거!
SELECT EMP.ENAME"사원명", EMP.DEPTNO"부서번호", EMP.HIREDATE"입사일", EMP.SAL"급여"
     , (1) "부서내입사별급여누적"
FROM EMP
ORDER BY 2,3;


SELECT E1.ENAME"사원명", E1.DEPTNO"부서번호", E1.HIREDATE"입사일", E1.SAL"급여"
     , (1) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 2,3;

SELECT E1.ENAME"사원명", E1.DEPTNO"부서번호", E1.HIREDATE"입사일", E1.SAL"급여"
     , (SELECT SUM(E2.SAL)
        FROM EMP E2
        WHERE E2.DEPTNO = E1.DEPTNO
          AND E2.HIREDATE <= E1.HIREDATE) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
--==>>
/*
CLARK	10	1981-06-09	2450	2450
KING	10	1981-11-17	5000	7450
MILLER	10	1982-01-23	1300	8750

SMITH	20	1980-12-17	 800	800
JONES	20	1981-04-02	2975	3775
FORD	20	1981-12-03	3000	6775
SCOTT	20	1987-07-13	3000	10875
ADAMS	20	1987-07-13	1100	10875

ALLEN	30	1981-02-20	1600	1600
WARD	30	1981-02-22	1250	2850
BLAKE	30	1981-05-01	2850	5700
TURNER	30	1981-09-08	1500	7200
MARTIN	30	1981-09-28	1250	8450
JAMES	30	1981-12-03	 950	9400
*/


--○ EMP 테이블을 대상으로
--   입사한 사원의 수가 가장 많았을 때의
--   입사년월과 인원수를 조회할 수 있도록 쿼리문을 구성한다.
/*
---------- -------
 입사년월   인원수
---------- -------
 1981-02      2
 1981-09      2
 1981-12      2
 1987-07      2
---------- -------
*/

--①

SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
     , COUNT(*) "인원수"
FROM EMP
WHERE COUNT(*) = 2
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>> 에러 발생
--     (ORA-00934: group function is not allowed here)


SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
     , COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = 2;
--==>>
/*
1981-12	2
1981-09	2
1981-02	2
1987-07	2
*/


SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
     , COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (입사년월 기준 최대 인원);


-- 입사년월 기준 최대 인원
SELECT COUNT(*)
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>>
/*
1
2
1
2
2
1
1
1
2
1
*/


SELECT MAX(COUNT(*))
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>> 2




SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월", COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                   FROM EMP
                   GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'));
--==>>
/*
1981-12	2
1981-09	2
1981-02	2
1987-07	2
*/

--②
SELECT T1.입사년월, T1.인원수
FROM
(
    SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
         , COUNT(*) "인원수"
    FROM EMP
    GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
)T1
WHERE T.인원수 = (2);




SELECT T1.입사년월, T1.인원수
FROM
(
    SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
         , COUNT(*) "인원수"
    FROM EMP
    GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
)T1
WHERE T1.인원수 = (SELECT MAX(T2.인원수)
                  FROM 
                  (
                  SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
                       , COUNT(*) "인원수"
                  FROM EMP
                  GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
                  )T2
                 )
ORDER BY 1;
--==>>
/*
1981-02	2
1981-09	2
1981-12	2
1987-07	2
*/


--③
SELECT T1.입사년월, T1.인원수
FROM
(
    SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
         , COUNT(*) "인원수"
    FROM EMP
    GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
)T1
WHERE T1.인원수 = (SELECT MAX(COUNT(*))
                   FROM EMP
                   GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'))
ORDER BY 1;
--==>>
/*
1981-02	2
1981-09	2
1981-12	2
1987-07	2
*/