何かやってみるブログ

興味をもったこと、趣味のこと、技術について色々書きます。

LeetCode Department Highest Salaryを解いてみた

問題

https://leetcode.com/problems/department-highest-salary/

解いた

SELECT
    EED.name AS 'Department',
    Employee.name AS 'Employee',
    Employee.salary AS 'Salary'
FROM (
    SELECT
        EE.Max,
        Department.name
    FROM (
        SELECT
            MAX(salary) AS Max,
            departmentId
        FROM
            Employee
        GROUP BY
            (departmentId)) AS EE
        JOIN Department ON EE.departmentId = Department.id) AS EED,
    Employee
WHERE
    Employee.salary = EED.Max
    AND Employee.departmentId = (
        SELECT
            id
        FROM
            Department
        WHERE
            name = EED.name);

結果

f:id:s-takaya1027:20220315001953p:plain