#185. 部门工资前三高的所有员工

Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId

1
2
3
4
5
6
7
8
9
10
11
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+

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

1
2
3
4
5
6
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:

1
2
3
4
5
6
7
8
9
10
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

解释:

IT 部门中,Max 获得了最高的工资,Randy 和 Joe 都拿到了第二高的工资,Will 的工资排第三。销售部门(Sales)只有两名员工,Henry 的工资最高,Sam 的工资排第二。

#安装 Mysql

#安装 yum 源

去官网下RPM包MySQL Community Downloads

按照自己的 Linux 版本安装,我的是CentOS7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ hostnamectl
Static hostname: izbp1j6rl141pt0isn2vusz
Pretty hostname: iZbp1j6rl141pt0isn2vusZ
Icon name: computer-vm
Chassis: vm
Machine ID: 963c2c41b08343f7b063dddac6b2e486
Boot ID: 9da023d194834a8aa4c95d07edd77b93
Virtualization: kvm
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-1127.13.1.el7.x86_64
Architecture: x86-64
$ uname -srm
Linux 3.10.0-1127.13.1.el7.x86_64 x86_64
$ cat /proc/version
Linux version 3.10.0-1127.13.1.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) #1 SMP Tue Jun 23 15:46:38 UTC 2020
1
2
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum -y install mysql80-community-release-el7-3.noarch.rpm

#安装 mysql

1
yum install mysql-community-server -y

#启动

1
service mysqld start

#获取初始密码

1
2
$ grep "password" /var/log/mysqld.log
2020-08-14T10:35:15.878806Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: o%HZ6*EoVuDy

#登录

1
2
3
$ mysql -uroot -p
# 修改新密码
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

#创建数据库

1
2
3
CREATE DATABASE onns;
# 切换数据库
use onns;

#SQL 架构

啊啊啊啊啊啊啊这个字居然是灰色的我没看见,结果自己手打了一遍,我真是醉了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE IF NOT EXISTS Employee (
Id int,
Name varchar(255),
Salary int,
DepartmentId int
);

CREATE TABLE IF NOT EXISTS Department (
Id int,
Name varchar(255)
);

TRUNCATE TABLE Employee;

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('1', 'Joe', '85000', '1');

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('2', 'Henry', '80000', '2');

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('3', 'Sam', '60000', '2');

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('4', 'Max', '90000', '1');

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('5', 'Janet', '69000', '1');

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('6', 'Randy', '85000', '1');

INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES ('7', 'Will', '70000', '1');

TRUNCATE TABLE Department;

INSERT INTO Department (Id, Name)
VALUES ('1', 'IT');

INSERT INTO Department (Id, Name)
VALUES ('2', 'Sales');

#结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT *
FROM Employee e1
WHERE 3 > (
SELECT COUNT(DISTINCT `Salary`)
FROM `Employee` e2
WHERE e2.Salary > e1.Salary
);

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary
FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE 3 > (
SELECT COUNT(DISTINCT `Salary`)
FROM `Employee` e2
WHERE e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
);

#参考链接