184. 部门工资最高的员工 + join + in

184. 部门工资最高的员工

LeetCode_MySql_184

题目描述

题解分析

1.首先需要使用group by找出工资最高的值
2. 然后考虑到最高工资的可能有多位，所以使用in语句找到所有符合条件的员工
3. 最外层使用连接连表查询员工所在的部门名字。

代码实现

# Write your MySQL query statement below
select Department.Name as 'Department', Employee.Name as 'Employee', Salary
from
    Department join Employee on Employee.DepartmentId = Department.Id
where
    (Employee.DepartmentId, Salary) in
    (
        select DepartmentId, max(Salary) as Salary
        from Employee
        group by DepartmentId
    );