题目描述

对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列

建表语句:

1
2
3
4
5
6
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`));

输出描述:

emp_no salary rank
10005 94692 1
10002 94409 2
10010 94409 2
10001 88958 3
10007 88958 3
10004 74057 4

不看最后一个rank排名,那么这道题特别简单:

1
2
3
4
select s.emp_no, s.salary
from salaries s
where s.to_date = '9999-01-01'
order by s.salary desc, s.emp_no asc

但是题目偏偏加了一个rank工资排名。那么怎样才能获得这个排名呢?

在Java中我们可以直接使用循环来给目标值加排名,SQL查询语句中看来行不通。仅仅使用一张表似乎达不到目的,那就使用两张表吧。

既然不能使用循环,那么可以通过什么方法来表示排名,或者说是表示在自己的前面还有多少人(包括自己)

很明显了,我们可以通过两张表中salary的大小不同来获取在自己前面的人的个数,即s1.salary <= s2.salary。举个栗子:

有三个salary(6000,5000,4000, 4000),输出为s1.salary,那么当s1.salary = 6000时,s1.salary <= s2.salary 的值为6000,也就是一个,当s1.salary = 5000时,s1.salary <= s2.salary 的值为6000和5000两个。但是当s1.salary = 4000时,s1.salary <= s2.salary 的值就为(6000,5000,4000, 4000)四个,且4000是重复的,因此我们使用count(distinct s2.salary) rank 来去重,并计算个数,即排名,以此类推,输出s1.salary的情况下,有多少个s2.salary大于等于s1.salary就是s1.salary的排名

当然不要忘了group by s1.emp_no ,用来将员工编号分组,计算count()

最后结果为:

1
2
3
4
5
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