- 如果没有排名,很快写出来
select emp_no,salary from salaries order by salary desc,emp_no asc
10001|88958
10002|72527
10004|72527
10003|43311
- 要获取排名
/构造临时表/
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