SQL排名问题

一、任意名次

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

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

解题思路

思路一

利用max函数和子查询，首先通过子查询找出低于最大值的salary，然后对该子查询结果再次利用max函数，取得的最大值即为排名第二的salary值

select em.emp_no,sa.salary,em.last_name,em.first_name
from employees em join salaries sa 
on em.emp_no=sa.emp_no and sa.to_date='9999-01-01'
where sa.salary=(select max(salary) 
                 from salaries 
                 where salary <(select max(salary) from salaries 
                                where to_date='9999-01-01')
                 and to_date='9999-01-01')

思路二

利用inner join 的不等条件连接

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 
and  s.to_date='9999-01-01'
and s.salary = (
    select s1.salary
    from
    salaries s1
    join
    salaries s2 on s1.salary<=s2.salary 
    and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
    group by s1.salary
    having count(distinct s2.salary)=2 )

二、按要求排名

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列.(题源牛客sql编程)

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

解题思路

思路一

直接使用窗口函数，SQL中常用于排位的窗口函数有
row number(),rank(),dense_rank()和NTILE()
这里要求相同薪水排名相同，所以用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 rank,emp_no;

思路二

select s1.emp_no,s1.salary,count(distinct s2.salary)
from salaries s1,salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' 
and s1.salary<= s2.salary/*给定s1.salary求有多少个s2.salary大于它*/
group by s1.emp_no
order by s1.salary desc,s1.emp_no asc