184. Department Highest Salary / LeetCode, SQL, MySQL

문제

source: LeetCode

Write an SQL query to find employees who have the highest salary in each of the departments.
Return the result table in any order.

The query result format is in the following example.

 

 

 

제출답안(MySQL)

WITH max_sal_by_dpt AS (
    SELECT 
        departmentid, 
        MAX(salary) AS salary
    FROM employee
    GROUP BY departmentid
)

SELECT 
    d.name AS Department,
    e.name AS Employee,
    salary AS Salary
FROM 
    employee AS e
    JOIN department AS d
    ON e.departmentid = d.id
WHERE (departmentid, salary) IN (SELECT departmentid, salary FROM max_sal_by_dpt)

 

 

 

문제 바로가기(MySQL)