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

方法一： 用rank()窗口函数，去重排序

SELECT
  emp_no,
  salary,
  dense_rank () over (
    ORDER BY
      salary DESC
  )
FROM
  salaries
WHERE
  to_date = '9999-01-01';

方法二

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;