并列排序问题

数据：https://blog.csdn.net/qq_38560351/article/details/106331612
题目：
1. 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
MySQL实现方法：相当于Oracle中的dense_rank() over（）
方式一：

SET @rank=0;
SET @salary=-1;
SELECT emp_no,salary,
CASE
WHEN @salary = salary THEN @rank
WHEN @salary := salary THEN @rank:=@rank+1
END as rank
FROM salaries 
WHERE 1=1
AND to_date='9999-01-01'
ORDER BY salary DESC,emp_no;

方式二（与方式一 的思想相同）：

SELECT emp_no,salary,
@rank:=@rank+(@salary<>(@salary:=salary)) rank
FROM salaries as a,
(SELECT @rank:=0,@salary:=-1) as b
WHERE 1=1
AND a.to_date = '9999-01-01'
ORDER BY salary DESC,emp_no;

方式三（相对复杂，自连接）：

SELECT a.emp_no,a.salary,
count(DISTINCT b.salary) as rank
FROM salaries as a,salaries as b 
WHERE 1=1
AND a.to_date='9999-01-01'
AND b.to_date = '9999-01-01'
AND a.salary<=b.salary
GROUP BY a.emp_no
ORDER BY a.salary DESC,a.emp_no;

Oracle实现方法：
使用dense_rank() over ([partition by * ] order by * [desc]) 方法。

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