184. 部门工资最高的员工

最新推荐文章于 2024-08-13 07:39:27 发布

原创最新推荐文章于 2024-08-13 07:39:27 发布 · 516 阅读

0 ·

CC 4.0 BY-SA版权

leetcode 专栏收录该内容

302 篇文章

订阅专栏

本文介绍如何使用SQL查询从Employee和Department表中找出每个部门薪资最高的员工，提供了两种有效的方法，一种是通过子查询匹配部门ID和最高薪资，另一种是先创建一个包含每个部门最高薪资的临时表。

摘要生成于 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            |
+----+-------+--------+--------------+

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

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

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

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

思路：一开始我这么写的

错误原因：I think in this case max() only gives the max salary of the group.所以当这一条数据中的薪水50,000，和中的最高薪水50,000相等的时候，也会被检索出来，就有问题了。为了避免这种情况的发生，应该把DepartmentId 也放进去，对应到具体的部门。

另外放两个条件的时候，注意 IN 前的列表和IN 后的列表顺序应该保持一致，否则就会报错。

SELECT  D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E, Department AS D
WHERE E.DepartmentId= D.Id
AND (DepartmentId, Salary) IN (SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY  DepartmentId)
ORDER BY E.Salary DESC

这么写顺眼多了：

SELECT  D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E, Department AS D
WHERE E.DepartmentId= D.Id 
AND Salary IN (SELECT MAX(Salary) FROM Employee AS E2 WHERE E2.DepartmentId=E.DepartmentId GROUP BY  DepartmentId)
ORDER BY E.Salary DESC

另一种思路，先把每个部门最高薪水及其所对应的DepartmentId存在一个T的表中：

SELECT  D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E, Department AS D, (SELECT DepartmentId, MAX(Salary) AS max FROM Employee GROUP BY  DepartmentId) AS T
WHERE E.DepartmentId= D.Id   --E，D连接起来
AND E.DepartmentId=T.DepartmentId --最高薪水所在部门要对应上
AND E.Salary=T.max ----最高薪水也要对应上