Leetcode - 184. Department Highest Salary

1. 문제

 

Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

 

Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table. It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.

 

Write a solution to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The result format is in the following example.

 

Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

https://leetcode.com/problems/department-highest-salary/

 

2. 문제 조건

 

1. find employees who have the highest salary in each of the departments. -> 부서별로 GROUP BY, MAX를 통해 hightest salary 찾기

 

3. 코드 작성

 

SELECT d.name AS Department
     , e.name AS Employee
     , m.max_salary AS Salary
FROM employee AS e
    JOIN (SELECT departmentId
        , MAX(salary) AS max_salary
        FROM employee
        GROUP BY departmentId) AS m ON e.departmentId = m.departmentId
                                        AND e.salary = m.max_salary
    JOIN department as d ON e.departmentId = d.id;

 

4. 회고

 

• 서브쿼리를 활용하여 JOIN을 할 수 있다는 사실을 알게 되었다.
  • 서브쿼리로 원하는 테이블을 만들어, 기존 테이블과 JOIN을 통해 문제에서 요구하는 데이터만 추출할 수 있다.