心得

這題是 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)

問題

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

答案

1. 這個方法是在Top Solutions看到的，在WHERE裡面統計有幾個其他同部門的人大於自己的薪水，把小於三個的都列出來，就是答案。

   # Write your MySQL query statement below
   SELECT  b.Name AS Department,
           a.Name AS Employee,
           a.Salary AS Salary
   FROM Employee AS a
   JOIN Department AS b
   ON a.DepartmentId = b.Id
   WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
           FROM Employee AS z
           WHERE a.DepartmentId = z.DepartmentId
           AND a.Salary < z.Salary) < 3

2. 同上，只是改成WHERE IN效能較佳

   # Write your MySQL query statement below
   SELECT  b.Name AS Department,
           a.Name AS Employee,
           a.Salary AS Salary
   FROM Employee AS a
   JOIN Department AS b
   ON a.DepartmentId = b.Id
   WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
           FROM Employee AS z
           WHERE a.DepartmentId = z.DepartmentId
           AND a.Salary < z.Salary) IN (0, 1, 2)