[MySQL][LeetCode][Hard] 185. Department Top Three Salaries

這題是 184. Department Highest Salary 的衍生題,但如果直接套用其方法的會TimeOut,不過還是記錄一下方法:

# Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     LIMIT 0, 1) AS TopSalary,
                    (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     AND y.Salary < TopSalary
                     LIMIT 0, 1) AS TwoSalary,
                    (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     AND y.Salary < TwoSalary
                     LIMIT 0, 1) AS ThreeSalary
        FROM Department AS z) AS b
ON a.DepartmentId = b.Id
WHERE ( a.Salary = b.TopSalary
OR      a.Salary = b.TwoSalary
OR      a.Salary = b.ThreeSalary)

Read More