#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
);

#参考链接