题目描述
![在这里插入图片描述](https://img-blog.csdnimg.cn/8a538527bc15454a81d2f681ecfad084.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
描述
有一个薪水表salaries简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/5a67abb157a64b8987be891904d86234.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
对所有员工的薪水按照salary降序进行1-N的排名,要求相同salary并列且按照emp_no升序排列:
![在这里插入图片描述](https://img-blog.csdnimg.cn/1e30810228de4231913acd16ad1dd9a1.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
示例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
题解
做题的做法
运行时间:14ms
超过73.52% 用Sqlite提交的代码
占用内存:3560KB
超过17.57%用Sqlite提交的代码
SELECT s1.emp_no, s1.salary, (
SELECT COUNT(DISTINCT s2.salary) + 1
FROM salaries s2
WHERE s2.salary > s1.salary
) AS t_rank
FROM salaries s1
ORDER BY salary DESC
;
MySQL实际还可以
SELECT emp_no, salary,
DENSE_RANK() OVER(ORDER BY salary DESC) AS t_rank
FROM salaries
ORDER BY t_rank, emp_no;