[oracle] GROUP BY - 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

 

 

 

 

 

SELECT A.EMP_NO, A.EMP_NAME, B.GRADE, A.SAL * B.AVG AS BONUS
FROM 
    HR_EMPLOYEES A
JOIN (
    SELECT EMP_NO, 
        CASE 
            WHEN AVG(SCORE) >= 96 THEN 0.2
            WHEN AVG(SCORE) >= 90 THEN 0.15
            WHEN AVG(SCORE) >= 80 THEN 0.1
            ELSE 0
        END AS "AVG",
        CASE 
            WHEN AVG(SCORE) >= 96 THEN 'S'
            WHEN AVG(SCORE) >= 90 THEN 'A'
            WHEN AVG(SCORE) >= 80 THEN 'B'
            ELSE 'C'
        END AS "GRADE"
    FROM HR_GRADE 
    GROUP BY EMP_NO
) B
ON A.EMP_NO = B.EMP_NO;