[리트코드] 185. Department Top Three Salaries - MySQL

https://leetcode.com/problems/department-top-three-salaries/

Department Top Three Salaries - LeetCode

---

• 문제
  • 각 부서의 급여순으로 3등까지 출력하라
  • 같은 급여가 여러명 있어도 등수는 1개이면, 없는 등수는 없다.

• 풀이
  • 등수 매기기 + 없는 등수는 없다. 
    • 윈도우 함수 DENSE_RANK 사용
  • 각 부서마다 급여 순위 매기기 + 3등 이내 출력

SELECT department.name AS department
     , employee.name AS employee
     , employee.salary
     , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS dr
FROM employee
    INNER JOIN department ON employee.departmentId = department.id

 

• 정답 코드

SELECT t.department
     , t.employee
     , t.salary
FROM (
    SELECT department.name AS department
         , employee.name AS employee
         , employee.salary
         , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS dr
    FROM employee
        INNER JOIN department ON employee.departmentId = department.id
) t
WHERE t.dr <= 3;