Sql实战 1.单表复用进行比较排名

时间：2020-07-28 11:06:30 阅读：75 评论：0 收藏：0 [点我收藏+]

题目描述

对所有员工的当前(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

解题思路：

其实求每个员工的当前薪水都比较容易，可以通过以下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思路不变，写法还可以简化一下：

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实战 1.单表复用进行比较排名

原文：https://www.cnblogs.com/Transkai/p/13388416.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)