[문제풀이] 프로그래머스 - 연간 평가점수에 해당하는 평가 등급

 

출처

프로그래머스

 

 

문제

HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블을 이용해 사원별 성과금 정보를 조회하려합니다. 평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.

평가등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS로 해주세요.
결과는 사번 기준으로 오름차순 정렬해주세요

문제에 대한 해석

조회 : 사번, 성명, 평가 등급, 성과금

정렬 : 사번 오름차순

성과금에 대한 기준

풀이(MYSQL)

-- 오답
select em.emp_no, em.emp_name,
case when gr.score >= 96 then 'S' 
    when gr.score >= 90 then 'A'
    when gr.score >= 80 then 'B'
    else 'C' end as GRADE, 
case when gr.score >= 96 then em.sal*0.2 
    when gr.score >= 90 then em.sal*0.15
    when gr.score >= 80 then em.sal*0.1
    else em.sal end as BONUS
from HR_DEPARTMENT dep join HR_EMPLOYEES em
on dep.dept_id = em.dept_id
join HR_GRADE gr
on em.emp_no = gr.emp_no
order by em.emp_no

 

쿼리의 결과를 보면 이름이 중복해서 나온다

 

그 이유는 이름별 상반기 하반기 점수가 모드 포함되어있다

 

상반기 하반기 점수의 평균을 구해서 다시 등급을 반영해 보자 

> 성과급의 기준이 평균이라는 말이 어디에도 없지만 다른 풀이를 보니 평균으로..

select em.emp_no, em.emp_name,
case when avg(gr.score) >= 96 then 'S' 
    when avg(gr.score)>= 90 then 'A'
    when avg(gr.score) >= 80 then 'B'
    else 'C' end as GRADE, 
case when avg(gr.score) >= 96 then em.sal*0.2 
    when avg(gr.score) >= 90 then em.sal*0.15
    when avg(gr.score)>= 80 then em.sal*0.1
    else em.sal end as BONUS
from HR_DEPARTMENT dep join HR_EMPLOYEES em
on dep.dept_id = em.dept_id
join HR_GRADE gr
on em.emp_no = gr.emp_no
group by em.emp_no, em.emp_name
order by em.emp_no

 

테스트는 성공으로 나오는데 제출하면 틀렸다고 한다..왜지 ..?

다른분 정답..이랑 똑같은데..

WITH HR_BONUS AS (
SELECT  EMP_NO
        ,CASE
        WHEN AVG(SCORE) >= 96 THEN 'S'
        WHEN AVG(SCORE) >= 90 THEN 'A'
        WHEN AVG(SCORE) >= 80 THEN 'B'
        ELSE 'C' END AS GRADE
        ,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 BONUS
  FROM  HR_GRADE
 GROUP
    BY  EMP_NO)


SELECT  HE.EMP_NO
        ,HE.EMP_NAME
        ,HB.GRADE
        ,HE.SAL*HB.BONUS AS BONUS
  FROM  HR_EMPLOYEES AS HE
  JOIN  HR_BONUS AS HB
    ON  HB.EMP_NO = HE.EMP_NO
 GROUP
    BY  EMP_NO

 

작동순서

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by ->결과반환

 

1. FROM 절: HR_DEPARTMENT테이블과 HR_EMPLOYEES 테이블을 dept_id을 기준으로 조인하고 HR_GRADE과 emp_no을 기준으로 조인한 테이블에서 
2. GROUP BY 절: emp_no , emp_name(부서명과 이름)으로 그룹화 하여 
3. SELECT 절: emp_no , emp_name를 조회하고, gr.score의 평균이 96보다 크면 S와 em.sal에서 0.2 를 곱하여 각각 'GEADE'와 'BONUS'컬럼에 저장하라
4. ORDER BY 절: emp_no의 오름차순