此处不允许使用分组函数_查找当前薪水排名第二多的员工信息（不使用order by和窗口函数）...-CSDN博客

题目描述

查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

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

输出描述：

解法一：在sqlite环境的解法

先将员工表和薪资表进行连接，得到员工的薪资情况；
再将两个员工薪资表进行连接，连接条件为b表的薪资大于a表的薪资，再根据员工号进行分组；
根据员工号分组后的，计算b表薪资出现的次数，假设比a表员工薪资高的有五个，那么b的薪资应该会出现五种不同的薪资

SELECT c.emp_no,c.salary,c.last_name,c.first_name
FROM
	(SELECT a.*,count(DISTINCT b.salary) AS 'cnt'
	 FROM
		(select aa.*,bb.salary
         from
               (select emp_no,last_name,first_name from employees) as aa
             inner join --- 员工信息表和薪资表进行连接
               (select emp_no,salary from salaries
                where to_date='9999-01-01') as bb on aa.emp_no=bb.emp_no
        ) as a
	 INNER JOIN   -- 两个员工薪资表进行连接
         (select cc.*,dd.salary
          from
                (select emp_no,last_name,first_name from employees) as cc
              inner join  --- 员工信息表和薪资表进行连接
                (select emp_no,salary from salaries
                where to_date='9999-01-01') as dd on cc.emp_no=dd.emp_no
         ) as b ON a.salary<=b.salary
		GROUP BY a.emp_no
    ) AS c
WHERE c.cnt=2;

解法二：在MySQL环境中

为了减少编写复杂的查询语句，首先创建一个视图；
然后在将两个表进行连接，求排名即可，和前面的步骤是一样的，只不过可以减少使用重复出现的查询语句。

CREATE VIEW emp_salary AS -- 创建视图
select a.*,b.salary
from
(select emp_no,last_name,first_name from employees) as a
inner join
(select emp_no,salary
from salaries
where to_date='9999-01-01') as b on a.emp_no=b.emp_no

SELECT c.emp_no,c.salary,c.last_name,c.first_name
FROM
	(SELECT a.*,count(DISTINCT b.salary) AS 'cnt'
	 FROM
		(SELECT * FROM emp_salary) as a
		INNER JOIN
		(SELECT * FROM emp_salary) as b ON a.salary<=b.salary
		GROUP BY a.emp_no) AS c
WHERE c.cnt=2