# 184. 部门工资最高的员工
# https://leetcode-cn.com/problems/department-highest-salary/

# SQL架构
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');

# Write your MySQL query statement below
select
    d.name as Department,
    e.name as Employee,
    e.salary as Salary
from
    Employee e
    join Department d on e.departmentId = d.id
where
    (e.departmentId, e.salary) in (
        select
            departmentId,
            max(salary)
        from
            Employee
        group by
            departmentId
    );

# clean-up
drop table Employee;
drop table Department;