题解-牛客网-SQL-(SQL18)查找当前薪水排名第二多的员工编号emp_no

1. 题目描述

1.1 输入

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

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

1.2 输出

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

2. 题目分析与解答

2.1 题目分析

根据查询要求，应当先找到salaries表中第二高的薪水，再以此作为WHERE子句的条件找到所有薪水排第二高的员工。
具体实现上，直接的想法是先用一个MAX函数找到salaries表中最高的薪水，在此基础上再用MAX函数即可找到salaries表中第二高的薪水。
另外还有一种思路，是通过salaries表的自连接查询得到第二高的薪水。salaries表自连接的语句如下：

SELECT
	s1.salary 
FROM
	salaries AS s1
	JOIN salaries AS s2 ON s1.salary <= s2.salary 
GROUP BY
	s1.salary

通过分析该语句的运行结果，可以看出其输出实际上有两列。左列为s1.salary，包括出现在salaries表中的每一个薪水；右列为s2.salary，与左列是多对一的关系，右列中的多行对应左列中的某一行，右列多行的薪水都符合大于或等于对应的左列某一行的薪水的条件。比如，左列中某一行的薪水为1000，其对应的右列应包括薪水为1000、1500、2000…到最高薪水的若干行。因此，可以推断出，如果左列中选择最高薪水的那一行，在排除右列中薪水重复的情况下只会对应于一行（即也是最高薪水的那一行）；如果左列中选择第二高的薪水的那一行，在排除右列中薪水重复的情况下可以对应于两行，包括最高薪水的那一行和第二高的薪水的那一行。根据此分析，可知通过该自连接语句得到第二高的薪水只在需再加上一个条件：COUNT( DISTINCT s2.salary ) = 2即可。

2.2 详细解答

根据题目分析可直接写出相应的SQL查询语句：

SELECT
	e.emp_no,
	s.salary,
	e.last_name,
	e.first_name 
FROM
	employees AS e
	JOIN salaries AS s ON e.emp_no = s.emp_no 
WHERE
	s.salary = (
	SELECT
		MAX( salary )   -- 在找到salaries表中最高薪水的基础上使用MAX函数即可找到第二高的薪水
	FROM
		salaries 
	WHERE
	salary < ( SELECT MAX( salary ) FROM salaries ));  -- 找到salaries表中最高的薪水

或：

SELECT
	e.emp_no,
	s.salary,
	e.last_name,
	e.first_name 
FROM
	employees AS e
	JOIN salaries AS s ON e.emp_no = s.emp_no 
WHERE
	s.salary = (
	SELECT
		s1.salary 
	FROM
		salaries AS s1
		JOIN salaries AS s2 ON s1.salary <= s2.salary
	GROUP BY
		s1.salary 
	HAVING
	COUNT( DISTINCT s2.salary ) = 2  -- 通过salaries表的自连接查询获取第二高的薪水
	);

这里也给出使用了ORDER BY子句的SQL查询语句：

SELECT
	e.emp_no,
	s.salary,
	e.last_name,
	e.first_name 
FROM
	employees AS e
	JOIN salaries AS s ON e.emp_no = s.emp_no 
WHERE
	s.salary = ( SELECT DISTINCT salary FROM salaries ORDER BY salary DESC LIMIT 1, 1 );