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 |
+--------+--------+--------+