问题: 求部门工资最高的员工
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 |
+------------+----------+--------+
解答:
mysql> select d.name as department,b.name as employee,b.salary from (select e.* from employee e inner join
-> (select max(salary) as max_salary,departmentid from employee group by departmentid) a
-> on e.salary=a.max_salary and e.departmentid=a.departmentid) b left join department d
-> on b.departmentid=d.id ;
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
2 rows in set (0.00 sec)
过程:
create table employee(
id int,
name varchar(20) not null,
salary int,
departmentid int not null,
primary key(id)) insert into employee(id,name,salary,departmentid) values ('','Joe','','');
insert into employee(id,name,salary,departmentid) values ('','Henry','','');
insert into employee(id,name,salary,departmentid) values ('','Sam','','');
insert into employee(id,name,salary,departmentid) values ('','Max','',''); create table department(
id int,
name varchar(20) not null,
primary key(id)) insert into department(id,name) values('','IT');
insert into department(id,name) values('','Sales'); select max(salary) as max_salary,departmentid from employee group by departmentid select e.* from employee e inner join
(select max(salary) as max_salary,departmentid from employee group by departmentid) a
on e.salary=a.max_salary and e.departmentid=a.departmentid select b.id,b.name,b.salary,d.name from (select e.* from employee e inner join
(select max(salary) as max_salary,departmentid from employee group by departmentid) a
on e.salary=a.max_salary and e.departmentid=a.departmentid) b left join department d
on b.departmentid=d.id select d.name as department,b.name as employee,b.salary from (select e.* from employee e inner join
(select max(salary) as max_salary,departmentid from employee group by departmentid) a
on e.salary=a.max_salary and e.departmentid=a.departmentid) b left join department d
on b.departmentid=d.id
--2020年5月10日 17点22分--
1. 部门工资最高的员工
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 |
+------------+----------+--------+