写在前面

• 原文地址：leetcode-cn.com/problems/de…

• 难度：中等（一看题目以为是简单，结果竟然是中等）

题目

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

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

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

思路

每个部门的最高工资是非常简单的（作为临时表）

select DepartmentId ,max(salary)
from employee
group by departmentid

但最终的输出不是这样子的，那应该如何整理下呢？

select d.name as Department ,e.name as Employee ,e.Salary 
from Employee e,Department d
where e.DepartmentId = d.id

结果的值和输出的关联就是 (d.id,e.salary) in ( select DepartmentId ,max(salary)）

最终语句就是：

select d.name as Department ,e.name as Employee ,e.Salary 
from Employee e,Department d
where e.DepartmentId = d.id
and (d.id,e.salary) in (
select DepartmentId ,max(salary)
from Employee 
group by DepartmentId)