题目来源:力扣(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
)