리트코드(LeetCode) MySQL-Department Highest Salary

select d.name as Department, e.name as Employee, salary as Salary
from Employee as e
inner join Department as d
on e.departmentID=d.id
where (departmentID,salary) in
        (select departmentID,max(salary) from Employee group by departmentID)

 

INNER JOIN에도 서브 쿼리를 쓸 수 있다.

select d.name as Department, 
        e.name as Employee, 
        salary as Salary
from Employee as e
    inner join (select departmentID,max(salary) as maximum from Employee group by departmentID) as m
    on m.departmentID=e.departmentID and m.maximum=e.salary
    inner join Department as d
    on e.departmentID=d.id

 

윈도우 함수를 이용하면

SELECT d.name as Department,
        maxs.name as Employee, 
        maxs.salary as Salary
FROM (
    SELECT *,  MAX(salary) over (partition by departmentId) as max_salary
    FROM Employee) as maxs
INNER JOIN Department as d
ON d.id=maxs.departmentId
WHERE maxs.salary = maxs.max_salary

SELECT d.name as Department,
        maxs.name as Employee, 
        maxs.salary as Salary
FROM (
    SELECT *,  rank() over (partition by departmentId order by salary desc) as rank
    FROM Employee) as maxs
INNER JOIN Department as d
ON d.id=maxs.departmentId
WHERE maxs.rank=1

max와 rank 모두 가능