#117. [LeetCode / MySQL] 185. Department Top Three Salaries

문제

 

입력 코드

1. SUBQUERY

SELECT d.name AS Department, 
       e.name AS Employee, 
       e.salary AS Salary
FROM Employee AS e
JOIN Department AS d
ON e.departmentId = d.id

WHERE 3 > (SELECT COUNT(DISTINCT e2.salary)
           FROM Employee AS e2
           WHERE e2.salary > e.salary AND e.departmentId = e2.departmentId)

 

 

2. WINDOW 함수 - DENSE_RANK()

SELECT Department, Employee, Salary
FROM (SELECT d.name AS Department,
             e.name AS Employee,
             e.salary AS Salary,
             DENSE_RANK() OVER (PARTITION BY d.name 
                                ORDER BY Salary DESC) AS rnk
      FROM Employee AS e
      JOIN Department AS d
      ON e.departmentId = d.id) AS rnk_tbl
WHERE rnk <= 3

WITH rnk_tbl AS (
    SELECT d.name AS Department,
           e.name AS Employee,
           e.salary AS Salary,
           DENSE_RANK() OVER (PARTITION BY e.departmentId
                              ORDER BY e.salary DESC) AS rnk
      FROM Employee AS e
      JOIN Department AS d
      ON e.departmentId = d.id
)

SELECT Department, Employee, Salary
FROM rnk_tbl
WHERE rnk <= 3

 

 

코드 설명

#SELECT #JOIN #ON #WHERE #COUNT #DISTINCT #WINDOW #DENSE_RANK() #PARTITION BY #ORDER BY #DESC #WITH

 

 

문제 출처

https://leetcode.com/problems/department-top-three-salaries/description/?envType=study-plan-v2&envId=top-sql-50