力扣SQL刷题11

题目：
编写一个 SQL 查询，找出每个部门工资最高的员工。对于下面的表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

解题思路：
（1）先对 DepartmentId 字段分组查询最大值，得到不同 DepartmentId 下的最大值

select max(Salary),DepartmentId 
from Employee 
group by DepartmentId

（2）再用DepartmentId 字段连接 Department 表，根据 Salary 和 DepartmentId 查找 Department.Name 字段

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