LeetCode知识点总结 - 184

LeetCode 184. Department Highest Salary

考点	难度
Database	Medium

题目

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

思路

先算出每个department的最高工资，再根据这个筛选。

答案

select Department.name as 'Department', Employee.name as 'Employee', salary
from Employee
join Department on Employee.DepartmentId = Department.Id
where (Employee.DepartmentId, Salary) in (
select DepartmentId, max(Salary)
from Employee
group by DepartmentId)