题目8：MySQL---------Department Highest Salary

最新推荐文章于 2025-07-20 18:08:41 发布

原创最新推荐文章于 2025-07-20 18:08:41 发布 · 760 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #leetcode #数据库MySQ--Leetcode

数据库MySQL 专栏收录该内容

18 篇文章

订阅专栏

本文介绍如何使用SQL查询来找出每个部门的最高薪酬员工，通过连接Employee和Department表，利用子查询筛选出各部最高薪酬者。

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  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | 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, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

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

创建表：

create table Employee 
(
  Id int         NOT      NULL AUTO_INCREMENT,
  Name           char(10) null,
  Salary         int      null,
  Departmentid   int      null,
  primary key (Id)
);

INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(1,"Joe",7000,1);
INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(2,"Henry",8000,2);
INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(3,"Sam",6000,2);
INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(4,"Max",9000,1);

create table Department
(
  Id    INT      NOT NULL auto_increment,
  Name  char(10) NULL,
  primary key (Id) 
);

insert into Department(Id, Name) values(1,"IT");
insert into Department(Id, Name) values(2,"Sales");

答案：

select D.name as Department1, E.name as Employee, E.Salary
from 
  Employee E,
  Department D,
  (select DepartmentId ,max(Salary) as max from Employee group by DepartmentId) T
where E.DepartmentId = T.DepartmentId
  and E.Salary = T.max
  and E.DepartmentId = D.id;