LeetCode 코딩 테스트 - Department Highest Salary(LV.Medium)

 

 

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.

 

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.

 

# 쿼리를 작성하는 목표, 확인할 지표 : 부서 별로 가장 높은 연봉을 가진 사람의 부서명과 이름 출력 / salary, departmentid, id
# 쿼리 계산 방법 : 1. join으로 부서이름 붙이기 -> 2. 부서별 최고연봉 구하기 -> 3. 부서별 최고연봉을 받는 사람의 정보(부서명,이름,연봉) 출력 
# 데이터의 기간 : x
# 사용할 테이블 : employee, department
# JOIN KEY : departmentid, id
# 데이터 특징 : x
SELECT
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM employee AS e
# 1
INNER JOIN department AS d 
ON e.departmentid = d.id
# 2, 3
WHERE
    (e.departmentid,e.salary) IN (SELECT departmentid, MAX(salary) FROM employee GROUP BY departmentid)