SQL217 對所有員工的薪水按照salary降序進行1-N的排名

xfcoding發表於2024-04-27
SQL


示例：

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

相關文章