【题目描述】
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)