[Programmers] 조건에 맞는 사원 정보 조회하기
정답
-- 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