LeetCode Department Highest Salary [ MySQL ]

Department Highest Salary - LeetCode

 

Subquery를 사용하여 가장 높은 salary를 구해 department 단위로 조인을 했습니다.

그리고 department name을 출력하기 위해 department와 inner join을 하였습니다.

 

SELECT
      d.name AS department
    , e.name AS employee
    , e.salary
FROM
    employee AS e
INNER JOIN
    (SELECT
        departmentid, MAX(salary) AS max_salary
    FROM
        employee
    GROUP BY
        departmentid) dh
ON
        e.departmentid = dh.departmentid
    AND e.salary = dh.max_salary
INNER JOIN
    department AS d 
ON 
    d.id = e.departmentid