首页 > 试题广场 >

对所有员工的薪水按照salary降序进行1-N的排名

[编程题]对所有员工的薪水按照salary降序进行1-N的排名

热度指数：344706 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

有一个薪水表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

算法知识视频讲解

ciphersaw

本题的主要思想是复用salaries表进行比较排名，具体思路如下：

1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = '9999-01-01'，挑选出当前所有员工的薪水情况。

2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。

3、千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()

4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

最后在支持ROW_NUMBER、RANK、DENSE_RANK等函数的SQL Server数据库中，有以下参考代码，可惜在本题的SQLite数据库中不支持。

SELECT emp_no, salaries, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
WHERE to_date = '9999-01-01' ORDER BY salary DESC, emp_no ASC

编辑于 2017-07-15 11:19:50 回复(92)

小海NB

使用非等值自连接方法：
SELECT
    A.emp_no,
    A.salary,
    (SELECT COUNT(DISTINCT B.salary) FROM salaries B WHERE
        B.salary >= A.salary 
        AND A.to_date = '9999-01-01'
        AND B.to_date = '9999-01-01') AS rank
FROM
    salaries A
WHERE 
    A.to_date = '9999-01-01'
ORDER BY
    rank, A.emp_no ASC

发表于 2019-07-03 13:46:51 回复(0)

宏力

看了下大家的代码，基本都与第一位大佬的代码相同，我这里分享一下另一种思路，运用的是rownum

select sa.emp_no,sa.salary,raa.rank
from salaries sa,
利用rownum 给每一条工资一个等级
(select salary,rownum rank
from (
查询所有to_date=9999-01-01且不重复的工资，根据工资排序，先去重后排序
select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc) ra )raa
得到工资及对应的等级，去除笛卡儿积，排序 输出
where sa.to_date='9999-01-01'
and sa.salary=raa.salary 
order by raa.rank,sa.emp_no;

编辑于 2017-11-26 16:16:45 回复(1)

Asoiaf

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank 
from salaries s1,salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<=s2.salary
group by s1.emp_no
order by rank

发表于 2017-07-08 22:18:39 回复(3)

｢只是｣最辣的那棵｢韭菜｣而已

本题的主要思想是复用salaries表进行比较排名，具体思路如下：

1、先对一张表的salary进行排序

select  emp_no ，salary

from salaries 

where to_date = '9999-01-01'

order by salary desc;

2、进行并列操作，加入 count

select  emp_no ，salary，count（salary）

from salaries 

where to_date = '9999-01-01'

order by salary desc;

3.这样只是统计了，这个salary出现的次数，并没有依照次序进行排序，要进行次序的排序，必须count,大于等于该条salary的数据条数，又因为数据有重复，所以distinct，此处必须使用表的重复使用功能

select  a.emp_no ，a.salary，

count（distinct b.salary）

from salaries as a,salaries as b

where a.to_date = '9999-01-01' 

and b.to_date ='9999-01-01'
and a.salary<= b.salary

order by salary desc;

3、因为使用了合计函数导致，count只返回一个值，表a选择返回的值却有好几个，所以必须进行分组查询

select  a.emp_no ，a.salary，

count（distinct b.salary）

from salaries as a,salaries as b

where a.to_date = '9999-01-01' 

and b.to_date ='9999-01-01'
and a.salary<= b.salary

group by a.emp_no
order by salary desc;

4、最后在s1.salary 逆序排之后，再以 s1.emp_no 顺序排列输出结果，必须满足第一个条件的情况下，满足第二个排序条件，等于进行的是相同的rank，数据有重复的值进行了emp_no的排序

最终结果
select  a.emp_no ，a.salary，

count（distinct b.salary）

from salaries as a,salaries as b

where a.to_date = '9999-01-01' 

and b.to_date ='9999-01-01'
and a.salary<= b.salary

group by a.emp_no
order by a.salary desc，a.emp_no asc;

编辑于 2017-11-11 16:36:48 回复(25)

杨家兴

如果是Oracle就可以使用rank()over()了，参考了前面人代码，换个思考，查询一个人的emp_no, 工资小于等于另一个人的个数，比另一个人低的个数是1（包括自己）是第一名，个数是2是第二名以此类推

编辑于 2017-07-10 15:12:06 回复(1)

指offer的剑在哪？

rank的输出使用COUNT(DISTINCE s2.salary)表示，其中s2也是指向salaries，表示的是比当前正在排名的s1的薪水大于等于的列值，这样就可以获取到了薪水的排名；

group by s1.emp_no必须加上，用于分组，不然的话最终结果就只有一列；


	SELECT de.dept_no, s.emp_no, s.salary 



	FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date ='9999-01-01'



	WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager)

发表于 2017-09-05 13:21:44 回复(0)

111野驴

如果面试的话，这么写会加分的：上面的思路太常规了

_{用了两个变量，变量使用时其前面需要加@，这里的：= 是赋值的意思，如果前面有Set关键字，则可以直接用=号来赋值，如果没有，则必须要使用:=来赋值，两个变量rank和pre，其中rank表示当前的排名，pre表示之前的工资，下面代码中的<>表示不等于，如果左右两边不相等，则返回true或1，若相等，则返回false或0。初始化rank为0，pre为-1，然后按降序排列工资，对于工资4来说，pre赋为4，和之前的pre值-1不同，所以rank要加1，那么工资4的rank就为1，下面一个分工资还是4，那么pre赋值为4和之前的4相同，所以rank要加0，所以这个工资4的rank也是1，以此类推就可以计算出所有工资的rank了}

SELECT emp_no,salary,

@rank := @rank + (@pre <> (@pre := salary)) Rank

FROM salaries, (SELECT @rank := 0, @pre := -1) INIT

WHERE to_date = '9999-01-01

group by emp_no

order by salary

编辑于 2017-08-28 00:34:47 回复(30)

此用户名涉嫌违规

这题重点技巧如何计算出【1，2，2，3】这种不跳数字的排序

1.自联结：

select t1.emp_no, t1.salary, count( distinct t2.salary) as t_rank
from salaries t1,salaries t2 
where t1.salary <= t2.salary
group by t1.emp_no,t1.salary
order by t1.salary desc, t1.emp_no

t1.salary <= t2.salary找出在查询t1.salary的时候有多少个t2.salary大于等于t1.salary。这里需要用distinct对t2.salary去重,不然出现的是【1，3，3，4】这种跳数字的排序。因为我们使用了聚合函数count()如果不加group by t1.emp_no的话，只会返回一条结果。同时goup by t1.salary也应该加上，因为salary属于列的字节名，不是主键且不唯一。而select子句只能存在常数、聚合函数、group by子句指定列(聚合键)。所以这里应该填上group by t1.salary。否则可能会出现记录不匹配的情况。

另一种写法（join）:

select t1.emp_no, t1.salary, count(distinct t2.salary) as t_rank
from salaries t1 join salaries t2 
    on t1.salary <= t2.salary
group by t1.emp_no,t1.salary
order by t1.salary desc, t1.emp_no

2.窗口函数：

select emp_no,
       salary,
       dense_rank() over(order by salary desc) as t_rank
from salaries

DENSE_RANK()是一个窗口函数，它为分区或结果集中的每一行分配排名，而排名值没有间隙。
行的等级从行前的不同等级值的数量增加1。

引用看到其他小伙伴的贡献：

1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

发表于 2021-03-24 14:18:09 回复(8)

雾z~霭

大神们代码中那个s1<=s2的结果，我想很多人和我一样很迷，于是我做了下总结，希望有帮助吧

select s1.emp_no, s1.salary, count(distinct s2.salary) rank
from salaries s1, salaries s2
where s1.salary <= s2.salary and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
group by s1.emp_no
order by rank

/*关于 s1.salary <= s2.salary的结果：
假设 salary: 1,2,3
那么比较是这样进行的
第一轮：
s1.1 <= s2.1; 成立则，s1=[1] s2=[1]
s1.2 <= s2.1; 不成立，s1,s2不增加元素
s1.3 <= s2.1; 不成立，s1,s2不增加元素

第二轮：
s1.1 <= s2.2; 成立 s1=[1,1] s2=[1,2]
s1.2 <= s2.2; 成立 s1=[1,1,2] s2=[1,2,2]
s1.3 <= s2.2; 不成立，s1,s2不增加元素

第三轮：
s1.1 <= s2.3; 成立 s1=[1,1,2，1] s2=[1,2,2,3]
s1.2 <= s2.3; 成立 s1=[1,1,2，1，2] s2=[1,2,2,3，3]
s1.3 <= s2.3; 成立 s1=[1,1,2，1，2，3] s2=[1,2,2,3，3,3]

从比较过程可以总结为：1、每一轮，左表的每一个元素都和右表的一个元素比较
2、如果等式成立，则左表元素被添加进s1，右表元素添加进s2
3、所以可以看到s1的值呈“交替”出现，而s2的值是“扎堆”出现

发表于 2018-08-16 22:47:04 回复(1)

zifangsky

其实求每个员工的当前薪水都比较容易，可以通过以下SQL来获取：

SELECT
	emp_no,
	salary
FROM
	salaries
WHERE
	to_date = '9999-01-01'
GROUP BY
	emp_no;

然后可以得到类似如下结果：

+--------+--------+
| emp_no | salary |
+--------+--------+
|  10001 |  88958 |
|  10002 |  72527 |
|  10003 |  43311 |
|  10004 |  74057 |
|  10005 |  94692 |
|  10006 |  43311 |
|  10007 |  88070 |
+--------+--------+

所以现在难点就是“如何按照salary进行按照1-N的排名”？

我想到的方案是对这个结果再次处理，比如构造两个上面结果这样的表，获取表2的 salary 大于等于表1 的 salary 的个数（因为同样 salary 算作一样的排名，所以需要去重处理），这样就得到了该员工的排名。也就是下面这个SQL：

SELECT
	result1.emp_no,
	result1.salary,
	COUNT(DISTINCT result2.salary) AS rank
FROM
	(
		SELECT
			emp_no,
			salary
		FROM
			salaries
		WHERE
			to_date = '9999-01-01'
		GROUP BY
			emp_no
	) AS result1,
	(
		SELECT
			emp_no,
			salary
		FROM
			salaries
		WHERE
			to_date = '9999-01-01'
		GROUP BY
			emp_no
	) AS result2
WHERE
	result2.salary >= result1.salary
GROUP BY
	result1.emp_no
ORDER BY
	result1.salary DESC,
	result1.emp_no ASC;

当然，这个SQL思路不变，写法还可以简化一下，也就变成了跟上面一些答案一样的SQL了：

SELECT
	s1.emp_no,
	s1.salary,
	COUNT(DISTINCT s2.salary) AS rank
FROM
	salaries s1,
	salaries s2
WHERE
	s1.to_date = '9999-01-01'
AND s2.to_date = '9999-01-01'
AND s2.salary >= s1.salary
GROUP BY
	s1.emp_no
ORDER BY
	s1.salary DESC,
	s1.emp_no ASC;

最后，这两个SQL都是可以AC的，欢迎使用。

编辑于 2019-08-13 14:05:05 回复(10)

渣渣……

select emp_no,salary, dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc

发表于 2018-08-09 14:43:19 回复(18)

xinxin666

SELECT emp_no,salary,
(SELECT COUNT(DISTINCT salary) FROM salaries s2 WHERE to_date='9999-01-01' AND s1.salary<=s2.salary)
AS rank
FROM salaries s1
WHERE s1.to_date='9999-01-01'
ORDER BY s1.salary DESC ,s1.emp_no ASC;

发表于 2017-09-29 21:06:00 回复(7)

1988coding

select s1.emp_no,s1.salary,count(distinct s2.salary)
from salaries s1,salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<= s2.salary/*给定s1.salary求有多少个s2.salary大于它*/
group by s1.emp_no
order by s1.salary desc,s1.emp_no asc

发表于 2017-08-30 00:40:31 回复(3)

LINDAMAN.

陈独秀同学请坐下

select a.emp_no,a.salary,(select count(distinct b.salary)from salaries b 
where b.to_date='9999-01-01' and b.salary>a.salary)+1 as rank
from salaries a where a.to_date='9999-01-01' 
group by emp_no 
order by salary desc,emp_no

发表于 2018-06-01 21:10:55 回复(7)

牛客665264546号

这样更简单，直接用dense_rank() 这个函数就可以的

select emp_no,salary,dense_rank()over(order by salary desc) as rank

from salaries
where to_date= '9999-01-01'

发表于 2020-06-07 20:04:56 回复(5)

。201908011734649

主要是在对于排名的理解上，分成两张表，一张表中该工资的排名其实就是表中大于等于该工资的数目，由于相同的salary排名相同，所有count中要有distinct ，由由于每个都要输出，要用group by ，否则应该是只有一条

发表于 2019-08-02 23:09:20 回复(2)

Krist1an

不难看出这考察的是窗口函数，对相同值采用相同排名

那么理所当然的想法是使用DENSE_RANK()这个窗口函数进行操作。

其代码如下：

SELECT emp_no, salary,
       DENSE_RANK()OVER(ORDER BY salary DESC) t_rank
FROM salaries

PARTITION BY是对内容的分组，但我们这个表中我们并不需要对员工进行分组，因此PARTITION BY是可以省略的。

然后我看评论里置顶的讨论好多同学实现的方法从代码上看都比较复杂，不知是否因为窗口函数在性能上比较弱？还是其他原因不推荐使用？

发表于 2021-09-06 00:12:02 回复(3)

rock201906190725710

select a.emp_no,a.salary,b.t_rank
from salaries a
left join (
select salary,rank() over (order by salary desc) as t_rank
from salaries
where to_date='9999-01-01'
group by salary
) b on a.salary=b.salary
where a.to_date='9999-01-01'
order by b.t_rank

发表于 2021-06-01 11:46:02 回复(0)