SQL217 对所有员工的薪水按照salary降序进行1-N的排名
- 题目
- 题解(125)
- 讨论(454)
- 排行
- 面经
new
较难 通过率:32.55% 时间限制:1秒 空间限制:32M
描述
有一个薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 88958 | 2002-06-22 | 9999-01-01 |
10002 | 72527 | 2001-08-02 | 9999-01-01 |
10003 | 43311 | 2001-12-01 | 9999-01-01 |
10004 | 72527 | 2001-12-01 | 9999-01-01 |
对所有员工的薪水按照salary降序先进行1-N的排名,如果salary相同,再按照emp_no升序排列:
emp_no | salary | t_rank |
10001 | 88958 | 1 |
10002 | 72527 | 2 |
10004 | 72527 | 2 |
10003 | 43311 | 3 |
示例1
输入:
drop table if exists `salaries` ; CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01'); INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');
复制输出:
10001|88958|1 10002|72527|2 10004|72527|2 10003|43311|3
开窗:
select
emp_no,salary,dense_rank() over(order by salary desc) t_rank
from
salaries
order by t_rank,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;