题目：

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

法一：窗口函数

select emp_no,salary,dense_rank() over(order by salary desc)
from salaries
WHERE to_date = '9999-01-01' 
ORDER BY salary DESC, emp_no ASC

法二：count 相关子查询

降序排名：比工资大的有几个，就是排第几

SELECT
  s1.emp_no,
  s1.salary,
  (SELECT  COUNT(DISTINCT s2.salary)
   FROM salaries s2
   WHERE s2.to_date = '9999-01-01'
        AND s2.salary >= s1.salary) AS rank
from salaries s1
where s1.to_date = '9999-01-01'
ORDER BY s1.salary DESC

法三：count 表连接

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