题目描述
对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
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 |
10009 | 94409 | 2 |
10010 | 94409 | 2 |
10001 | 88958 | 3 |
10007 | 88070 | 4 |
10004 | 74057 | 5 |
10002 | 72527 | 6 |
10003 | 43311 | 7 |
10006 | 43311 | 7 |
10011 | 25828 | 8 |
解题思路:
先看输出描述,可以看出排序实际上是dense_rank,SQL Server中可用dense_rank,在本题的SQLite中不可用。
首先将两个表连接,都以to_date='9999-01-01'为条件,关键思路是 count(distinct s2.salary) 和 s2.salary>=s1.salary,这表明选择大于或等于s1中工资的s2中工资的数量,用distinct去重。一定要加“=”,否则就不包括s1.salary自己,以及与其相等的其他salary,得出来的rank就会少1。
然后用emp_no分组,不分组结果就会只显示第一条或最后一条记录。
最后用order by 先按salary逆序排,再用emp_no顺序排。
代码:
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