185. Department Top Three Salaries - 部门工资前三高的所有员工＜Hard＞

最新推荐文章于 2021-12-07 10:01:00 发布

原创最新推荐文章于 2021-12-07 10:01:00 发布 · 238 阅读

0 ·

CC 4.0 BY-SA版权

LeetCode - sql 专栏收录该内容

15 篇文章

订阅专栏

本文介绍如何使用SQL查询来找出每个部门中薪资排名前三的员工。通过具体实例展示了两种不同的查询方法，并对每种方法进行了详细解析。

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

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

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
The Department table holds all departments of the company.
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 (order of rows does not matter).
Explanation:In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

分析：公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。

AC代码：根据https://blog.youkuaiyun.com/tfstone/article/details/109180115以前的思路：

貌似leetcode没按题目给定的输出顺序检测测~，测试用例输出的结果是错的，但是能AC..我们还是按照标准给排下序把~

SELECT d.name AS `Department`, e.name AS `Employee`, e.salary
FROM employee e 
JOIN department d ON e.departmentid = d.id 
WHERE e.salary IN ( 
    SELECT salary FROM( 
        SELECT DISTINCT salary FROM employee 
        WHERE departmentid = d.id 
        ORDER BY salary DESC LIMIT 3 
    ) AS a 
)
order by e.departmentid,e.salary desc

#注意使用left join - 当左表不为空，右表为空，左关联会出现有返回值的情况；

select b.Name as `Department`,a.Name as `Employee`,a.Salary
from Employee a
join Department b on b.Id = a.DepartmentId
where 3 > (
    select count(distinct b.Salary) from Employee b 
    where a.DepartmentId = b.DepartmentId and b.Salary > a.Salary 
)
order by a.DepartmentId,a.Salary desc

or

select b.Name as `Department`,a.Name as `Employee`,a.Salary
from Employee a, Department b
where b.Id = a.DepartmentId and 3 > (
    select count(distinct b.Salary) from Employee b 
    where a.DepartmentId = b.DepartmentId and b.Salary > a.Salary 
)
order by a.DepartmentId,a.Salary desc