【leetcode】部门工资前三高的员工

最新推荐文章于 2024-03-10 09:51:38 发布

一个酸菜鱼

最新推荐文章于 2024-03-10 09:51:38 发布

阅读量326

点赞数

本文链接：https://blog.youkuaiyun.com/lin_ff/article/details/84995756

版权

本文介绍如何使用SQL查询从Employee和Department表中找出每个部门薪资排名前三的员工，包括具体的SQL语句和实现思路。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 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            |
+----+-------+--------+--------------+

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

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

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

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

思路：

1、先用left join 连接两张表；

2、获取各部门的分组数据；

3、获取3条纪录（每个部门工资相同算一个记录）

4、按照部门id,工资降序；

select d.Name as Department,e.Name as Employee,e.Salary from Employee e left join Department d on e.DepartmentId=d.Id where e.DepartmentId is not null and d.Id is not null and (e.DepartmentId) in (select DepartmentId from Employee group by DepartmentId order by Salary desc) and (select count(distinct ee.Salary) from Employee ee where ee.DepartmentId is not null and ee.DepartmentId=d.Id and e.Salary<=ee.Salary)<=3 order by d.Id,e.Salary desc;