📚 Study/Oracle
Oracle :: 서브 상관 쿼리
bono-hye
2023. 10. 29. 22:58
■ 서브 상관 쿼리
- 메인 쿼리가 있는 테이블의 컬럼이 서브 쿼리의 조건절(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
*/