如何使用SQL检测数值随时间下降并查询薪资降低的员工姓名

场景说明 #

涉及的数据库结构如下：

基础关联查询的结果结构如下：

你已编写的基础关联SQL语句：

SELECT employees.emp_no, first_name, last_name, salary, from_date, to_date, hire_date 
from employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no;

实现方案 #

检测薪资下降的核心逻辑为：对同一员工的薪资记录按生效时间排序，判断是否存在后生效的薪资低于前一次薪资的情况，符合条件的员工即为目标结果。

方案1：窗口函数实现（适配MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的主流数据库） #

通过LAG窗口函数分组取同员工上一次的薪资值，对比筛选后去重即可：

SELECT DISTINCT e.first_name, e.last_name
FROM employees e
INNER JOIN (
    SELECT
        emp_no,
        salary,
        -- 按员工编号分组，按薪资生效日期升序排序，取同组上一条记录的薪资值
        LAG(salary) OVER(PARTITION BY emp_no ORDER BY from_date) AS prev_salary
    FROM salaries
) s ON e.emp_no = s.emp_no
-- 筛选当前薪资低于上一次薪资的记录
WHERE s.salary < s.prev_salary;

方案2：自关联实现（适配不支持窗口函数的老版本数据库，如MySQL 5.x） #

通过薪资表自关联匹配同员工不同时段的薪资记录，对比筛选后去重即可：

SELECT DISTINCT e.first_name, e.last_name
FROM employees e
INNER JOIN salaries s1 ON e.emp_no = s1.emp_no
-- 关联同员工更晚生效的薪资记录
INNER JOIN salaries s2 ON e.emp_no = s2.emp_no AND s2.from_date > s1.from_date
-- 筛选晚生效薪资低于更早薪资的记录
WHERE s2.salary < s1.salary;

内容的提问来源于stack exchange，提问作者Michel Machado