心得
這題是 184. Department Highest Salary 的衍生題,但如果直接套用其方法的話會TimeOut,不過還是記錄一下方法:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
# 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.
12345678910 +----+-------+--------+--------------+| 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.
123456 +----+----------+| 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.
123456789 +------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 || IT | Randy | 85000 || IT | Joe | 70000 || Sales | Henry | 80000 || Sales | Sam | 60000 |+------------+----------+--------+
答案
- 這個方法是在Top Solutions看到的,在
WHERE
裡面統計有幾個其他同部門的人大於自己的薪水,把小於三個的都列出來,就是答案。
1234567891011# Write your MySQL query statement belowSELECT b.Name AS Department,a.Name AS Employee,a.Salary AS SalaryFROM Employee AS aJOIN Department AS bON a.DepartmentId = b.IdWHERE ( SELECT COUNT(DISTINCT(z.Salary))FROM Employee AS zWHERE a.DepartmentId = z.DepartmentIdAND a.Salary < z.Salary) < 3 - 同上,只是改成
WHERE IN
效能較佳
1234567891011# Write your MySQL query statement belowSELECT b.Name AS Department,a.Name AS Employee,a.Salary AS SalaryFROM Employee AS aJOIN Department AS bON a.DepartmentId = b.IdWHERE ( SELECT COUNT(DISTINCT(z.Salary))FROM Employee AS zWHERE a.DepartmentId = z.DepartmentIdAND a.Salary < z.Salary) IN (0, 1, 2)