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

1. 如果没有排名，很快写出来
   select emp_no,salary from salaries order by salary desc,emp_no asc

10001|88958
10002|72527
10004|72527
10003|43311

2. 要获取排名
   /构造临时表/
   select *
   from salaries sa1,salaries sa2
   order by sa1.salary desc,sa1.emp_no ASC

10001|88958|2002-06-22|9999-01-01|10001|88958|2002-06-22|9999-01-01
10001|88958|2002-06-22|9999-01-01|10002|72527|2001-08-02|9999-01-01
10001|88958|2002-06-22|9999-01-01|10003|43311|2001-12-01|9999-01-01
10001|88958|2002-06-22|9999-01-01|10004|72527|2001-12-01|9999-01-01
10002|72527|2001-08-02|9999-01-01|10001|88958|2002-06-22|9999-01-01
10002|72527|2001-08-02|9999-01-01|10002|72527|2001-08-02|9999-01-01
10002|72527|2001-08-02|9999-01-01|10003|43311|2001-12-01|9999-01-01
10002|72527|2001-08-02|9999-01-01|10004|72527|2001-12-01|9999-01-01
10004|72527|2001-12-01|9999-01-01|10001|88958|2002-06-22|9999-01-01
10004|72527|2001-12-01|9999-01-01|10002|72527|2001-08-02|9999-01-01
10004|72527|2001-12-01|9999-01-01|10003|43311|2001-12-01|9999-01-01
10004|72527|2001-12-01|9999-01-01|10004|72527|2001-12-01|9999-01-01
10003|43311|2001-12-01|9999-01-01|10001|88958|2002-06-22|9999-01-01
10003|43311|2001-12-01|9999-01-01|10002|72527|2001-08-02|9999-01-01
10003|43311|2001-12-01|9999-01-01|10003|43311|2001-12-01|9999-01-01
10003|43311|2001-12-01|9999-01-01|10004|72527|2001-12-01|9999-01-01

3.当前排名： 工资 大于等于 临时表当前值的个数 （最终版）

select sa1.emp_no,sa1.salary,count(distinct sa2.salary)
from salaries sa1,salaries sa2
where sa1.salary<=sa2.salary group by sa1.emp_no
order by sa1.salary desc,sa1.emp_no ASC