# 185. 部门工资前三高的所有员工
# https://leetcode-cn.com/problems/department-top-three-salaries/

# 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', '85000', '1');
insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1');
insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1');
insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1');
insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '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,
    e1.name as Employee,
    e1.salary as Salary
from
    Employee e1
    join Department d on e1.departmentId = d.id
where
    (
        select
            count(distinct e2.salary)
        from
            Employee e2
        where
            e2.salary > e1.salary
            and e1.departmentId = e2.departmentId
    ) < 3;

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