面试题：查询部门最高工资的员工信息

最新推荐文章于 2024-10-17 21:40:37 发布

weixin_30314793

最新推荐文章于 2024-10-17 21:40:37 发布

阅读量506

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wan1976/p/4755910.html

本文提供两种SQL解决方案，用于从Employee和Department表中找出每个部门薪资最高的员工信息。示例包括IT部门的Max和Sales部门的Henry。

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

难度：中等

表 Employee 保存了所有的员工数据。

+----+-------+--------+--------------+
| 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查找出各部门工资最高的员工信息。如上所示，IT部门工资最高的是Max，Sales部门工资最高的是Henry。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

答案一：

SELECT D.Name as Department, E1.Name as Employee, E1.Salary as Salary
FROM Employee E1 join Department D
WHERE E1.DepartmentId = D.Id and E1.Salary >= (SELECT MAX(Salary) from Employee E2 WHERE E1.DepartmentId = E2.DepartmentId);

答案二：

SELECT b.Name as Department, a.name as Employee, a.Salary as Salary
FROM Employee a, Department b, (select max(Salary) as Salary, DepartmentId from Employee group by DepartmentId) c
WHERE a.DepartmentId = b.Id and a.Salary = c.Salary and a.DepartmentId = c.DepartmentId

转载于:https://www.cnblogs.com/wan1976/p/4755910.html