获取当前薪水第二多的员工的emp_no以及其对应的薪水salary[SQL练习题]

本文链接：https://blog.youkuaiyun.com/yunAike/article/details/145442552

描述

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Chirstian	Koblick	M	1986-12-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-26	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	74057	2001-11-27	9999-01-01

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

emp_no	salary	last_name	first_name
10004	74057	Koblick	Chirstian

示例1

输入：

drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

输出：

10004|74057|Koblick|Chirstian

思路：

不用 `ORDER BY` 的解决方案

要找出薪水排名第二的员工信息，可先找出最高薪水，再找出小于最高薪水的最大值，也就是第二高薪水，最后根据第二高薪水查找对应的员工信息。

答案：

-- 找出最高薪水
WITH max_salary AS (
    SELECT MAX(salary) AS max_sal
    FROM salaries
),
-- 找出小于最高薪水的最大值，即第二高薪水
second_max_salary AS (
    SELECT MAX(salary) AS second_max
    FROM salaries
    WHERE salary < (SELECT max_sal FROM max_salary)
),
-- 连接 employees 表和 salaries 表
joined_data AS (
    SELECT
        employees.emp_no,
        salaries.salary,
        employees.last_name,
        employees.first_name
    FROM
        employees 
        JOIN salaries ON employees.emp_no = salaries.emp_no
)
-- 根据第二高薪水筛选出对应的员工信息
SELECT 
    emp_no,
    salary,
    last_name,
    first_name
FROM 
    joined_data
WHERE 
    salary = (SELECT second_max FROM second_max_salary);

代码解释

max_salary CTE：借助 MAX() 函数找出 salaries 表中的最高薪水。
second_max_salary CTE：在 salaries 表中筛选出薪水小于最高薪水的记录，再用 MAX() 函数找出其中的最大值，此为第二高薪水。
joined_data CTE：把 employees 表和 salaries 表通过 emp_no 字段连接起来，选取员工编号、薪水、姓氏和名字。
主查询：从 joined_data 中筛选出薪水等于第二高薪水的记录。

复杂度分析

时间复杂度：主要由三个部分构成。找出最高薪水的时间复杂度为 O(n) ，其中 n 是 salaries 表的记录数；找出第二高薪水的时间复杂度同样为 O(n)；连接两个表并筛选出符合条件的记录的时间复杂度为O(n) 。所以，总体时间复杂度为 O(n)。
空间复杂度：主要取决于中间结果集的大小，也就是三个 CTE 的大小。由于每个 CTE 存储的结果集大小和输入数据规模成正比，因此空间复杂度为 O(n)。