184. 部门工资最高的员工

题目

Employee 表包含所有员工信息,每个员工有其对应的Id, salary 和 department Id。

Id Name Salary DepartmentId
1 Joe 70000 1
2 Jim 90000 1
3 Henry 80000 2
4 Sam 60000 2
5 Max 90000 1

Department表包含公司所有部门的信息。

Id Name
1 IT
2 Sales

编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。

Department Employee Salary
IT Max 90000
IT Jim 90000
Sales Henry 80000

解释:
Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。

题解

SELECT `Department`.`Name` AS `Department`, `Employee`.`Name` AS `Employee`, `Employee`.`Salary` AS `Salary`
FROM `Employee`
         INNER JOIN (
    SELECT MAX(`Salary`) AS `Salary`, `DepartmentId` FROM `Employee` GROUP BY `DepartmentId`
) `t` ON `t`.`DepartmentId` = `Employee`.`DepartmentId` AND `Employee`.`Salary` = `t`.`Salary`
         INNER JOIN `Department` ON `t`.`DepartmentId` = `Department`.`Id`