【LeetCode】数据库 - 部门工资最高的员工

【题目描述】

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  |
+------------+----------+--------+

【题目解答】

首先建表、生成数据

drop table if exists employee;
drop table if exists department;

create table `employee` (
	`id` int(11) not null auto_increment,
	`name` char(20) not null,
	`salary` int(20) not null,
	`departmentid` int(11) not null,
	primary key(`id`)
)engine=innodb charset=utf8;

create table `department` (
	`id` int(11) not null auto_increment,
	`name` char(20) not null,
	primary key(`id`)
)engine=innodb charset=utf8;

insert into employee(name, salary, departmentid) value("joe", 70000, 1),("8enry", 60000, 2),("sam", 60000, 2),("max", 90000, 1);
insert into department(name) value("IT"),("sales");

结果如下

mysql> select d. name as department, e. name as employee, e.salary from departme
nt d, employee e where e.departmentid = d.id and e.salary = ( select max(salary)
 from employee where departmentid = d.id );
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| sales      | henry    |  80000 |
| IT         | max      |  90000 |
+------------+----------+--------+
2 rows in set (0.00 sec)