不用开窗标号

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;