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

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

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

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

 

这行代码的意思是使用 `SalaryServiceImpl` 中的 `selectByEmpid` 方法查询出符合条件的 `Salary` 对象集合,并将结果赋值给 `ArrayList<Salary>` 类型的变量 `salaries`。其中,`select.get(num).getEmpId()` 表示获取 `select` 集合中索引为 `num` 的元素的 `empId` 属性值,作为 `selectByEmpid` 方法的参数,用于查询该员工的薪资信息。 假设 `SalaryServiceImpl` 的 `selectByEmpid` 方法是按照员工编号 `empId` 查询该员工的薪资信息的,其代码可能类似于下面这样: ```java public List<Salary> selectByEmpid(int empId) { List<Salary> list = new ArrayList<>(); Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { // 获取数据库连接 conn = getConnection(); // 创建 PreparedStatement 对象 stmt = conn.prepareStatement("select * from salary where emp_id = ?"); stmt.setInt(1, empId); // 执行查询操作 rs = stmt.executeQuery(); // 将查询结果封装成 Salary 对象列表 while (rs.next()) { Salary salary = new Salary(); salary.setId(rs.getInt("id")); salary.setEmpId(rs.getInt("emp_id")); salary.setAmount(rs.getBigDecimal("amount")); salary.setPayDate(rs.getDate("pay_date")); list.add(salary); } } catch (SQLException e) { // 异常处理 } finally { // 关闭数据库连接 close(conn, stmt, rs); } return list; } ``` 在上面的代码中,我们首先获取数据库连接,然后创建 `PreparedStatement` 对象并设置查询参数,执行查询操作并封装查询结果,最后关闭数据库连接并返回查询结果。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值