Letcode---SQL练习---部门工资最高的员工

最新推荐文章于 2024-07-24 20:01:29 发布

茗少

最新推荐文章于 2024-07-24 20:01:29 发布

阅读量144

点赞数

CC 4.0 BY-SA版权

分类专栏： Letcode SQL

本文链接：https://blog.youkuaiyun.com/qq_41896040/article/details/98450228

Letcode 同时被 2 个专栏收录

18 篇文章

订阅专栏

SQL

11 篇文章

订阅专栏

本文介绍了一种SQL查询方法，用于找出每个部门中薪酬最高的员工。通过联接Employee和Department表，并使用子查询来确定每个部门的最大薪资，从而得到结果。

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

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
The Department table holds all departments of the company.

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

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

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

SQL代码：

select d.Name as Department,e.Name as Employee,e.Salary
from Employee e join Department d
on e.DepartmentId=d.Id
where (e.DepartmentId,e.Salary) in(
    select DepartmentId,max(Salary)
    from Employee
    group by(DepartmentId)
)