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

心得

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

# 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

+----+-------+--------+--------------+
>
> | 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

+----+----------+
>
> | 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

答案

  1. 這個方法是在Top Solutions看到的,在WHERE裡面統計有幾個其他同部門的人大於自己的薪水,把小於三個的都列出來,就是答案。
1
2
3
4
5
6
7
8
9
10
11
12

# 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
  1. 同上,只是改成WHERE IN效能較佳
1
2
3
4
5
6
7
8
9
10
11
12

# 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)