LeetCode 로 공부하기 184. Department Highest Salary

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.

 

문제해결1

select d.name as Department, e.name as Employee, e.Salary
from employee as e
join department as d on e.departmentid = d.id
where (e.departmentid, e.salary)
in(select departmentid, max(salary) as 'max_salary' from Employee group by departmentId);​

 

문제해결2 윈도우 함수 활용

select ms.department, ms.name as Employee, ms.salary
from(
    select e.name, e.salary, d.name as department ,
       max(salary) over(partition by departmentid) as max_salary
    from employee as e
    join department as d on e.departmentid = d.id
    ) as ms
where ms.salary = ms.max_salary;