题目8：MySQL---------Department Highest Salary-优快云博客

本文介绍如何使用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  | 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;