LeetCode-184. 部门工资最高的员工（中等）

题目来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

本人思路：
方法一：
– 1、先找到每个部门工资最高的员工，输出员工和最高工资，构成临时表temp
– 2、将Employee表与temp表内连接，输出所有工资大于等max_salary的员工

select temp.department as Department, e1.Name as employee ,e1.salary
from Employee as e1
inner join 
(
select d.id as id ,d.Name as department,max(salary) as max_salary
from Employee as e inner join Department as d
on e.DepartmentId =d.id 
group by d.id 
)temp
on e1.DepartmentId = temp.id
where e1.salary >= temp.max_salary

方法二：
1、将temp表放在where条件中，直接进行判断

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