题目描述
对所有员工的当前(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`));
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 |
解题思路：
其实求每个员工的当前薪水都比较容易，可以通过以下SQL来获取：
SELECT emp_no, salary FROM salaries WHERE to_date = ‘9999-01-01‘ GROUP BY emp_no;
|
1
2
3
4
5
6
7
8
9
10
11
|
+--------+--------+| emp_no | salary |+--------+--------+| 10001 | 88958 || 10002 | 72527 || 10003 | 43311 || 10004 | 74057 || 10005 | 94692 || 10006 | 43311 || 10007 | 88070 |+--------+--------+ |
我想到的方案是对这个结果再次处理，比如构造两个上面结果这样的表，获取表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;
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;