题目描述
对所有员工的当前(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;