SQL实现员工薪资降序排名的多种方法
问题描述
我们需要为员工薪资表中的所有记录按照薪资金额从高到低进行1-N的排名，相同薪资的员工应获得相同的排名。
数据表准备
首先创建员工薪资表并插入测试数据：
DROP TABLE IF EXISTS `employee_salary`;
CREATE TABLE `employee_salary` (
`employee_id` int(11) NOT NULL,
`salary_amount` int(11) NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
PRIMARY KEY (`employee_id`,`start_date`));
INSERT INTO employee_salary VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO employee_salary VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO employee_salary VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO employee_salary VALUES(10004,72527,'2001-12-01','9999-01-01');
传统SQL解决方案
在不支持窗口函数的MySQL版本中，我们可以通过自关联表的方式实现排名功能。核心思路是统计每个员工的薪资有多少其他员工的薪资大于或等于它。
SELECT e1.employee_id, e1.salary_amount as current_salary, e2.salary_amount as compared_salary
FROM employee_salary e1
JOIN employee_salary e2 ON e1.salary_amount <= e2.salary_amount
ORDER BY e1.employee_id ASC;
上述查询会生成笛卡尔积，我们需要对结果进行分组和去重处理，最终得到排名：
SELECT tt.employee_id, e.salary_amount, tt.ranking
FROM (
SELECT e1.employee_id, COUNT(DISTINCT e2.salary_amount) as ranking
FROM employee_salary e1, employee_salary e2
WHERE e1.salary_amount <= e2.salary_amount
GROUP BY e1.employee_id) tt
JOIN employee_salary e ON e.employee_id = tt.employee_id
ORDER BY tt.ranking ASC;
查询结果将显示每个员工的ID、薪资及其排名，薪资高的员工排名靠前。
使用窗口函数解决方案
在MySQL 8.0及以上版本中，我们可以使用窗口函数更简洁地实现排名功能：
SELECT
employee_id,
salary_amount,
dense_rank() over(ORDER BY salary_amount DESC) as salary_rank
FROM employee_salary;
常用排名窗口函数介绍
以下是三种常用的排名窗口函数及其区别：
- RANK()：当有相同值时，会跳过后续排名号。例如，3个员工并列第1名，则下一名是第4名。
- DENSE_RANK()：当有相同值时，不会跳过后续排名号。例如，3个员工并列第1名，则下一名是第2名。
- ROW_NUMBER()：为每一行分配唯一的连续排名号。例如，即使值相同，也会分配不同的排名号（1,2,3...）。
窗口函数的基本语法为：<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)，其中方括号内的内容可以省略。
扩展：实现RANK()功能
如果我们需要使用基础SQL实现RANK()的功能，可以考虑以下方法：
SELECT tt.employee_id, e.salary_amount, tt.ranking + 1 as salary_rank
FROM (
SELECT e1.employee_id, COUNT(e2.salary_amount) as ranking
FROM employee_salary e1
LEFT JOIN employee_salary e2 ON e1.salary_amount < e2.salary_amount
GROUP BY e1.employee_id) tt
JOIN employee_salary e ON e.employee_id = tt.employee_id
ORDER BY ranking ASC;
这种方法通过LEFT JOIN确保薪资最高的员工也能被统计到（此时计数为0），最终结果加1得到正确的排名。
