I am an amazing person.

최대 1 분 소요

문제 링크

정답

-- HR_DEPARTMENT: DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION
-- HR_EMPLOYEES: EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL
-- HR_GRADE: EMP_NO, YEAR, HALF_YEAR, SCORE

WITH TOTAL_SCORE AS (
    SELECT EMP_NO, SUM(SCORE) AS TOTAL_SCORE
    FROM HR_GRADE
    WHERE YEAR = 2022
    GROUP BY EMP_NO
    ORDER BY 2 DESC
    LIMIT 1
)

SELECT T.TOTAL_SCORE AS SCORE, E.EMP_NO, E.EMP_NAME, E.POSITION, E.EMAIL
FROM HR_EMPLOYEES AS E
JOIN TOTAL_SCORE AS T ON E.EMP_NO = T.EMP_NO;

✅ Point

  • cte를 통한 점수 합산 및 최고득점자 계산
  • join을 통해 최고득점자에 대한 정보 가져오기
  • 단, 현재 풀이의 한계: 최고득점자가 여러 명인 경우를 반영하지 못함 (LIMIT 1 로 제한을 두었기 때문)

    다른 풀이

    ```sql – HR_DEPARTMENT: DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION – HR_EMPLOYEES: EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL – HR_GRADE: EMP_NO, YEAR, HALF_YEAR, SCORE

WITH RANKED AS ( SELECT EMP_NO, SUM(SCORE) AS SCORE, RANK() OVER (ORDER BY SUM(SCORE) DESC) AS R FROM HR_GRADE WHERE YEAR = 2022 GROUP BY EMP_NO )

SELECT R.SCORE, E.EMP_NO, E.EMP_NAME, E.POSITION, E.EMAIL FROM RANKED R JOIN HR_EMPLOYEES E ON R.EMP_NO = E.EMP_NO WHERE R = 1; ```

  • RANK 함수를 이용하여 순위 계산
    • RANK() OVER (ORDER BY SUM(SCORE) DESC) AS R

참고