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

描述

有一个薪水表salaries简况如下

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

 

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,
  s1.emp_no ;