LeetCode 184. 部门工资最高的员工

摘要：
考察GROUP BY、多表联查、多字段 IN 查询。

题目

GROUP BY + 临时表 + 多表联查

MySQL

select d.name as Department, e.name as Employee, e.salary as Salary
from 
(
select e.departmentId, max(e.salary) as salary
from Employee as e
group by e.departmentId
) as temp
left join Employee as e 
on e.departmentId = temp.departmentId
and e.salary = temp.salary
left join Department as d 
on e.departmentId = d.id;

对 Employee 表 departmentId 字段分组查询最高薪资作为临时表。
使用该临时表关联 Employee 查到每个 departmentId 和最高薪资对应员工信息。
同时关联 Department 表查询部门信息。

左连接的使用：临时表来自于 Employee 表，所以左连接查询右表一定存在值。
临时表左连接 Department 表查询部门信息时，右表可能存在空值，我们可以允许。

GROUP BY + IN + 临时表

MySQL

select d.name as Department, e.name as Employee, e.salary as Salary
from Employee as e
left join Department as d 
on e.departmentId = d.id
where (e.departmentId, e.salary) in
(
    select departmentId, max(salary) as salary
    from Employee 
    group by departmentId
);

先关联 Employee 和 Department 两张表组合成一张大表，再使用 IN 条件限定范围。
使用 GROUP BY 对 Employee 表按照 DepartmentId 分组求最大薪资划定限定范围。

有趣的是多字段 IN 的使用。

原题链接： LeetCode 184. 部门工资最高的员工