SQL23 对所有员工的薪水按照salary降序进行1-N的排名

描述

有一个薪水表salaries简况如下:

对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列，且按照emp_no升序排列：

/* 
题目：对所有员工的薪水按照salary降序进行1-N的排名，
要求相同salary并列，且按照emp_no升序排列：*/

-- 方法一：窗口函数
select s.emp_no,s.salary,dense_rank() over(order by s.salary desc) as t_rank
from salaries s
where to_date='9999-01-01'
-- order by s.emp_no asc


-- 方法二：不使用窗口函数,用非等值连接和自连接

select s1.emp_no,s1.salary,COUNT(DISTINCT s2.salary) AS t_rank
from salaries s1, salaries s2
where  s1.salary <= s2.salary
group by 
   s1.salary,s1.emp_no
order by 
   s1.salary desc

-- 方法三：不使用窗口函数,用子查询
   select s1.emp_no,s1.salary,
   (select 
    COUNT(DISTINCT s2.salary) AS t_rank 
    from salaries s2 
    where s1.salary <= s2.salary)
   from salaries s1
   order by 
   s1.salary desc
    */