LeetCode department-top-three-salariesを解いてみた

問題

解いた

SELECT
    Department,
    Employee,
    Salary
FROM (
    SELECT
        ( SELECT DISTINCT
                Salary
            FROM (
                SELECT
                    DENSE_RANK() OVER (ORDER BY Salary DESC) AS 'rank',
                    Salary
                FROM
                    Employee
                WHERE
                    Employee.departmentId = DD.id) E
            WHERE
                E.rank = 1) AS NO1, ( SELECT DISTINCT
                Salary
            FROM (
            SELECT
                DENSE_RANK() OVER (ORDER BY Salary DESC) AS 'rank', Salary
            FROM
                Employee
            WHERE
                Employee.departmentId = DD.id) E
        WHERE
            E.rank = 2) AS NO2, ( SELECT DISTINCT
            Salary
        FROM (
        SELECT
            DENSE_RANK() OVER (ORDER BY Salary DESC) AS 'rank', Salary
        FROM
            Employee
        WHERE
            Employee.departmentId = DD.id) E
    WHERE
        E.rank = 3) AS NO3, DD.name AS 'Department', EE.name AS 'Employee', EE.salary AS 'Salary'
FROM
    Department DD
    JOIN Employee EE ON DD.id = EE.departmentId) SalaryData
WHERE
    NO1 = Salary
    OR NO2 = Salary
    OR NO3 = Salary