[Leetcode/SQL] 184. Department Highest Salary

문제는 여기서 볼 수 있습니다.

 

SQL Schema

Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)
Create table If Not Exists Department (id int, name varchar(255))
Truncate table Employee
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')

 

Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key column for this table.
departmentId is a foreign key 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 for this table.
Each row of this table indicates the ID of a department and its name.

 

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

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.

 

 

Code:

SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM Employee e
LEFT JOIN Department d
ON e.departmentId = d.id
WHERE (e.departmentId, e.salary) IN
      (SELECT departmentId, max(salary)
       FROM Employee
       GROUP BY departmentId
      );

처음에는 where절 없이, group by e.departmentId having e.salary=max(e.salary) 구문을 사용했는데, ["Sales", "Henry", 80000] 만 나왔다. 이 경우 그룹별 첫 row만 확인하고 having 조건에 맞지 않으면 select되지 않고 다음 그룹 비교로 넘어갔다.

또 select절에 max(salary)라고 할 경우, ['IT', 'Joe', 90000] 처럼 존재하지 않는 row가 select되었다.

 

따라서 where-in 구문을 활용하여 departmentId와 salary를 동시에 만족시키도록 하는 코드를 추가해야 한다.