¡á¿¹ 4_3_1 || Merge ÇÔ¼ö¸¦ ÀÌ¿ëÇÑ µ¥ÀÌÅÍ Ã³¸® ¿¹ 1
MERGE INTO BONUSES D -- 󸮴ë»óÅ×À̺í
USING ( SELECT EMPLOYEE_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80 ) S -- Á¤º¸Á¦°øÁýÇÔ
ON ( D.EMPLOYEE_ID = S.EMPLOYEE_ID ) -- ¿¬°á°í¸®
WHEN MATCHED THEN
____UPDATE SET D.BONUS = D.BONUS+S.SALARY*.01 -- Á¸Àç ½Ã Ã³¸®
DELETE WHERE (S.SALARY > 8000)
WHEN NOT MATCHED THEN
INSERT ( D.EMPLOYEE_ID, D.BONUS ) -- ºÎÀç½Ã ó¸®
VALUES ( S.EMPLOYEE_ID, S.SALARY*0.1 );
¡á¿¹ 4_3_2 || Merge ÇÔ¼ö¸¦ ÀÌ¿ëÇÑ µ¥ÀÌÅÍ Ã³¸® ¿¹ 2
MERGE INTO POR_VOC_LOG_INFO D
USING ( SELECT :IN_LOG_YMD LOG_YMD, :IN_LOG_EMP_NO LOG_EMP_NO
FROM DUAL ) S
ON ( D.LOG_YMD = S.LOG_YMD
AND D.LOG_EMP_NO = S.LOG_EMP_NO )
WHEN MATCHED THEN
UPDATE SET____ D.LOG_CNT = D.LOG_CNT+1
WHEN NOT MATCHED THEN
INSERT (D.LOG_YMD, D.LOG_EMP_NO, D.LOG_CNT)
VALUES (:IN_LOG_YMD, :IN_LOG_EMP_NO, 1);
¡á¿¹ 4_3_3 || ºÐ¼® ÇÔ¼öÀÇ »ç¿ë ¿¹
SELECT SABUN, ENG_NAME, JOIN_GBN_CODE,
COUNT(SABUN) OVER(PARTITION BY JOIN_GBN_CODE
ORDER BY SABUN) AS COUNT_MEM
FROM INSA;
¡á¿¹ 4_3_4 || SALARYÀÇ µ¥ÀÌÅÍ °ªÀ¸·Î ¼øÀ§ ¸Å±â´Â ÇÔ¼öµé »ç¿ë ¿¹
SELECT ROWNUM, T1.*
FROM ( SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE,
RANK() OVER( PARTITION BY JOIN_GBN_CODE
ORDER BY SALARY DESC ) AS RANK,
DENSE_RANK() OVER( PARTITION BY JOIN_GBN_CODE
ORDER BY SALARY DESC ) AS DENSE_RANK,
ROW_NUMBER() OVER( PARTITION BY JOIN_GBN_CODE
ORDER BY SALARY DESC ) AS ROW_NUMBER
FROM INSA
) T1
WHERE JOIN_GBN_CODE = 'RGL'
AND ROWNUM <= 10;
¡á¿¹ 4_3_5 || TOP-N ºÐ¼® ÇÔ¼öÀÇ »ç¿ë ¿¹
--SQL1
SELECT ROWNUM, T1.*
FROM ( SELECT SABUN, ENG_NAME,
NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY,
JOIN_GBN_CODE
FROM INSA
WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013'
ORDER BY SABUN DESC ) T1
WHERE JOIN_GBN_CODE = 'RGL'
AND ROWNUM <=5;
--SQL2
SELECT T1.*
FROM ( SELECT SABUN, ENG_NAME,
NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY,
JOIN_GBN_CODE,
ROW_NUMBER() OVER( PARTITION BY JOIN_GBN_CODE
ORDER BY JOIN_DAY DESC ) AS ¼øÀ§
FROM INSA
WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013'
ORDER BY SABUN DESC
) T1
WHERE JOIN_GBN_CODE = 'RGL'
AND ¼øÀ§ <= 5
ORDER BY ¼øÀ§;
¡á¿¹ 4_3_6 || INDEX »ç¿ëÀ¸·Î RANK Á¶È¸
SELECT ROWNUM, T1.*
FROM
( SELECT /*+ INDEX_DESC(INSA INSA_PK) */
SABUN, ENG_NAME,
NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY,
JOIN_GBN_CODE
FROM INSA
WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013'
) T1
WHERE JOIN_GBN_CODE = 'RGL'
AND ROWNUM <=5;