SQL217. 对所有员工的薪水按照salary降序进行1-N的排名
描述
| 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 |
| 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
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3360KB, 提交时间: 2021-07-29
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;
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3368KB, 提交时间: 2021-11-22
select emp_no, salary, dense_rank()over(order by salary desc) t_rank from salaries
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2021-09-23
select emp_no,salary,dense_rank() over (order by salary desc)from salaries;
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2021-09-16
select emp_no,salary,dense_rank()over(order by salary desc)as t_rank from salaries
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2021-09-08
-- rank排名：查询表中大于自己薪水的员工的数量（考虑并列：去重）
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 ;