SQL217 对所有员工的薪水按照salary降序进行1-N的排名

描述

示例：

drop table if exists `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');

基础解法

在 MySQL 8.0 之前，没法使用窗口函数，所以要考虑不使用窗口函数如何实现。
本题的主要思想是表的自关联，通过两张表 salary 的比对就可以知道表 1 的 salary 比表 2 的 salary 低的数据有多少了。
比如，通过 s1.salary <= s2.salary，可得到 salary2 这一列是对于每个员工有多少员工比自己薪水高的数据。

SELECT s1.emp_no, s1.salary salary1, s2.salary salary2
FROM salaries s1
JOIN salaries s2 ON s1.salary <= s2.salary
ORDER BY s1.emp_no ASC;
+--------+---------+---------+
| emp_no | salary1 | salary2 |
+--------+---------+---------+
|  10001 |   88958 |   88958 |
|  10002 |   72527 |   72527 |
|  10002 |   72527 |   88958 |
|  10002 |   72527 |   72527 |
|  10003 |   43311 |   72527 |
|  10003 |   43311 |   88958 |
|  10003 |   43311 |   72527 |
|  10003 |   43311 |   43311 |
|  10004 |   72527 |   72527 |
|  10004 |   72527 |   88958 |
|  10004 |   72527 |   72527 |
+--------+---------+---------+

然后分组后使用 distinct 排除自己得到了排名,最终答案：

SELECT tt.emp_no, s.salary, tt.t_rank
FROM (
  SELECT s1.emp_no, COUNT(DISTINCT s2.salary) t_rank
  FROM salaries s1, salaries s2
  WHERE s1.salary <= s2.salary
  GROUP BY s1.emp_no) tt
JOIN salaries s ON s.emp_no = tt.emp_no
ORDER BY tt.t_rank ASC;
+--------+--------+--------+
| emp_no | salary | t_rank |
+--------+--------+--------+
|  10001 |  88958 |      1 |
|  10004 |  72527 |      2 |
|  10002 |  72527 |      2 |
|  10003 |  43311 |      3 |
+--------+--------+--------+

窗口函数解法

SELECT 
  emp_no,
  salary,
  dense_rank() over(ORDER BY salary DESC) `t_rank` 
FROM salaries;

窗口函数简介

下面介绍三种用于进行排序的专用窗口函数：
1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······
2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······
窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ] ORDER BY <排序用列清单> ），其中[ ]中的内容可以忽略。

扩展

用基础sql实现RANK()功能，我目前想到的，还没验证：

SELECT tt.emp_no, s.salary, tt.t_rank + 1
FROM (
	SELECT s1.emp_no, COUNT(s2.salary) t_rank
	FROM salaries s1
	LEFT JOIN salaries s2 ON s1.salary < s2.salary
	GROUP BY s1.emp_no) tt
JOIN salaries s ON s.emp_no = tt.emp_no
ORDER BY t_rank ASC;

如果使用 join，排第一的查不出来，使用 left join 的话就显示 0，然后查询结果加 1 就行了。