【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            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

【题目解答】

首先建表、生成数据

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),("henry", 80000, 2),("sam", 60000, 2),("max", 90000, 1),("janet", 69000, 1),("randy", 85000, 1);
insert into department(name) value("IT"),("sales");

首先将两个表连接到一起，再按照department、salary来降序排名

mysql> select department. name as department, e1. name as employee, e1.salary as
 salary from employee e1 join department on e1.departmentid = department.id orde
r by department. name, e1.salary desc;
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| IT         | max      |  90000 |
| IT         | randy    |  85000 |
| IT         | joe      |  70000 |
| IT         | janet    |  69000 |
| sales      | henry    |  80000 |
| sales      | sam      |  60000 |
+------------+----------+--------+
6 rows in set (0.00 sec)

然后找出每个不同的department中的前三个salary，新建一张表employee e2，比较这张表和e1的departmentid相等的记录中e2.salary > e1.salary的记录的个数小于3个的记录

结果如下

mysql> select department. name as department, e1. name as employee, e1.salary as
 salary from employee e1 join department on e1.departmentid = department.id wher
e ( select count(distinct e2.salary) from employee e2 where e2.salary > e1.salar
y and e1.departmentid = e2.departmentid ) < 3 order by department. name, e1.sala
ry desc;
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| IT         | max      |  90000 |
| IT         | randy    |  85000 |
| IT         | joe      |  70000 |
| sales      | henry    |  80000 |
| sales      | sam      |  60000 |
+------------+----------+--------+
5 rows in set (0.00 sec)