Leetcode 185. Department Top Three Salaries

Posted on In Views: Disqus:
没什么，最近刷了一遍Leetcode上面数据库的免费题目，将解决方法记录一下。

185. Department Top Three Salaries

原题目链接：185. Department Top Three Salaries

Employee表有所有员工的信息。每一位员工有一个Id，salary，也有一列表示部门Id（DepartmentId）。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department表包含公司的所有的部门。

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

写一条SQL查询语句找出每一个部门当中薪酬排名前三的员工。对于上面的表格，SQL查询语句返回的结果如下：

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

Solution

方法：采用子查询JOIN语句[Accepted]

Algorithm

在公司当中排名前三的薪酬，意味着在这个公司当中不超过三个薪酬比当前薪酬高的员工了。

1
2
3
4
5
6
7
8
9
select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)
;

在这段代码当中，我们累加大于e1.Salary的薪酬个数。因此对于样本数据它的输出如下：

| Employee | Salary |
|----------|--------|
| Henry    | 80000  |
| Max      | 90000  |
| Randy    | 85000  |

然后，我们需要将Employee表和Department表相链接得到部门信息。

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

结果如下：

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