首页 > 试题广场 >

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

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

热度指数：330480 时间限制：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)

头发茂密bug男

我的mysql 环境是 5.7.11 牛客的环境是3.7.9

我自己本地运行结果是正常的牛客就一直报语法错误

我是真的无奈了（先排序，再利用虚拟变量得到排名这是我的想法）

SELECT
a.emp_no,
a.salary,
CASE
WHEN @temp_salary = a.salary THEN
@temp_rank
WHEN @temp_salary := a.salary THEN
@temp_rank := @temp_rank + 1
END AS rank
FROM
(
SELECT
emp_no,
salary
FROM
salaries
WHERE
to_date = '9999-01-01'
ORDER BY
salary DESC,
emp_no ASC
) AS a,
(
SELECT
@temp_rank := 0 ,@temp_salary := NULL
) r

编辑于 2019-04-03 16:21:03 回复(8)

梦龙吃梦龙

1、关键词：等级排序、相同salary并列、emp_no升序

2、语句

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

3、本题的关键在于自身进行比较，如果某员工的薪水小于或等于按薪水进行降序排名第三的薪水时，该员工的薪水则为第三。

发表于 2020-08-03 00:15:11 回复(0)

小海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 回复(22)

李李李

本题的主要思想是复用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 顺序排列输出结果

发表于 2017-10-23 20:47:03 回复(4)

杨家兴

如果是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 回复(9)

渣渣……

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)