[leetcode] MySQL > 185. Department Top Three Salaries

leetcode Database 185. Department Top Three Salaries

---

SQL Schema & Explanation

The Employee table holds all employees.
Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
Write a SQL query to find employees who earn the top three salaries in each of the department.
For the above tables, your SQL query should return the following rows
(order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

 

출처 : leetcode Database 185. Department Top Three Salaries

Department Top Three Salaries - LeetCode

 

---

나의 풀이

# Write your MySQL query statement below
SELECT d.Name Department, e.Name Employee, e.Salary Salary
FROM Employee e JOIN Department d ON e.DepartmentId = d.Id
WHERE e.Salary IN (
    SELECT * FROM (
        SELECT DISTINCT(Salary) 
        FROM Employee 
        WHERE DepartmentId = d.Id 
        ORDER BY Salary DESC LIMIT 3
    ) AS _
)

 

봉급 순위 TOP 3를 뽑는 것이 문제였습니다.

단, 봉급이 같은 사람이 존재하여도 TOP 3 안에 들면 같이 뽑는 조건이 조금 까다로웠습니다.

Employee와 Department를 조인한 테이블에서 Salary의 값이 Distinct(Salary), ORDER BY Salary DESC Limit 3를 이용하여 봉급이 높은 순서로 뽑은 Top 3에 포함되면 출력하는 쿼리를 통해 문제를 해결했습니다.

 

왜 굳이 IN문 뒤에 SELECT * FROM을 넣은 이유는 그냥 Limit을 이용한 서브쿼리는 해당 MySQL 버전에서 사용할 수 없어서 Select * FROM문을 넣어줌으로써 이를 해결했습니다.