牛客网SQL-查找当前薪水排名第二多的员工，不准使用order by

题目描述

查找当前薪水(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`));

求解

解法1：

采用嵌套子查询和MAX

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e
join salaries s
on e.emp_no=s.emp_no
where s.salary=(select max(salary)
              from salaries 
              where salary<(select max(salary)
                            from salaries 
                            where to_date='9999-01-01'));

解法2：

采用嵌套子查询和自连接，但不使用MAX
having子句中count等于几就可以找第几高的

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e
join salaries s
on e.emp_no=s.emp_no
where s.salary=(select s1.salary
                from salaries s1
                join salaries s2
                on s1.salary <= s2.salary
                where s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
                group by s1.salary
                having count(distinct s2.salary)=2);

更新

对比题目：对所有员工的薪水按照salary进行按照1-N的排名
对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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`));

求解

解法1：

采用dense_rank()函数

SELECT emp_no, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
from salaries
WHERE to_date = '9999-01-01' 
ORDER BY salary DESC, emp_no ASC

解法2：

和上题类似，采用自连接（也是非等值连接）

select s1.emp_no,s1.salary,count(distinct s2.salary) ran
from salaries s1
join salaries s2
on s1.salary<=s2.salary
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
group by s1.emp_no
order by s1.salary desc,s1.emp_no;

注：两题核心都是salaries表的自连接，精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2，也即上题的排名第二，其余排名以此类推。