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

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

1. +----+-------+--------+--------------+
2. | Id | Name | Salary | DepartmentId |
3. +----+-------+--------+--------------+
4. | 1 | Joe | 70000 | 1 |
5. | 2 | Henry | 80000 | 2 |
6. | 3 | Sam | 60000 | 2 |
7. | 4 | Max | 90000 | 1 |
8. +----+-------+--------+--------------+

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

1. +----+----------+
2. | Id | Name |
3. +----+----------+
4. | 1 | IT |
5. | 2 | Sales |
6. +----+----------+

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

1. +------------+----------+--------+
2. | Department | Employee | Salary |
3. +------------+----------+--------+
4. | IT | Max | 90000 |
5. | Sales | Henry | 80000 |
6. +------------+----------+--------+

例1：

select d.Name as Department,e.Name as Employee,e.Salary
from Department d,Employee e
where e.DepartmentId=d.Id and e.Salary=(Select max(Salary) from Employee where DepartmentId=d.Id)

例2:

select d.Name as Department,e.Name as Employee,Salary 
from Employee e join Department d on e.DepartmentId=d.Id 
where (e.Salary,e.DepartmentId)
 in (select max(Salary),DepartmentId from Employee group by DepartmentId) 

例3:

select d.Name Department ,e1.Name Employee,e1.Salary Salary 
from Employee e1
inner join Department d on e1.DepartmentId= d.Id
wheree1.Salary in (select Max(e2.Salary) from Employee e2 where e2.DepartmentId =e1.DepartmentId)