牛客 sql23 对所有员工的当前(to_date='9999-

题目描述
对所有员工的当前(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.按照1-n排名，排名需要计数，这里使用两个表相交计算 s1.salary <= s2.salary 有多少个，就是第几名，因此是 count(distinct s2.salary) 个

sql答案

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

使用变量的方法 牛客中 暂不支持

SELECT emp_no,salary,
@rank := @rank + (@pre <> (@pre := salary)) Rank
FROM salaries, (SELECT @rank := 0, @pre := -1) INIT
WHERE to_date = '9999-01-01
group by emp_no
order by salary

使用窗口函数

select emp_no,salary,dense_rank() over( order by salary desc) as rank
from salaries 
where to_date= '9999-01-01'