[MySQL] 윈도우함수로 예제 문제 풀어보기 (LeetCode - department-highest-salary)

문제

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.

문제 코드

/* Write your T-SQL query statement below */
SELECT total.department AS Department,
       total.name AS Employee,
       total.salary AS Salary
FROM(
    SELECT E.name,
           E.salary,
           D.name AS department,
           Max(salary) OVER (PARTITION BY DepartmentId) AS MaxSalary
    FROM Employee AS E
            INNER JOIN Department AS D ON E.departmentid = D.id
    ) total
WHERE total.salary = total.MaxSalary

문제 해석

먼저 INNER JOIN을 통해 employee의 departmentid 와 department의 id값이 같은 행들을 합친다. 그 후 윈도우 함수를 통해 departmentid를 기준으로 max salary값을 구해온다. 그후 서브쿼리를 이용하여 해당 문제 조건에 맞춰 작성하면 된다.