SQL查询员工最后薪资信息时last_salary列结果异常问题

我有一张包含worker_id、month、basic_salary字段的数据表，需要编写SQL脚本查询每个员工的worker_id、最后薪资日期、倒数第二薪资日期以及最后薪资金额。我编写了如下CTE脚本：

with cte as (
  select WORKER_ID,  (select max(month) from workers_table) last_salary
    , ROW_NUMBER()over (partition by worker_id order by month) rn
  from workers_table
  group by WORKER_ID,month
)
select *
from cte 
where rn=2

执行后得到的结果中，last_salary列数据不正确，无法获取预期结果，执行结果示例：

WORKER_ID   last_salary rn
2011-11-11  2022-01-04  2
2011-11-12  2022-01-04  2
2011-11-13  2022-01-04  2
2011-11-14  2022-01-04  2
2011-11-15  2022-01-04  2
2011-11-16  2022-01-04  2

问题分析 #

1. 子查询(select max(month) from workers_table)未按worker_id过滤，取的是全表所有员工的最大月份，而非当前员工自己的最后薪资日期，这是last_salary列数据错误的核心原因。
2. 原逻辑仅筛选rn=2的记录，只能拿到员工倒数第二的薪资记录，无法同时获取最后薪资的日期和金额，不符合需求。
3. 原CTE中的GROUP BY WORKER_ID,month无实际意义，因为worker_id+month通常是薪资记录的唯一标识，无需额外分组。

正确解决方案 #

方案一：条件聚合（推荐，兼容单条记录的员工）

通过窗口函数给每个员工的薪资记录按日期倒序排名，再用条件聚合将同一员工的最新、倒数第二薪资信息合并到一行：

WITH salary_ranks AS (
    SELECT 
        worker_id,
        month,
        basic_salary,
        -- 按员工分组，日期倒序排名，rn=1是最新薪资，rn=2是倒数第二
        ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month DESC) AS rn
    FROM workers_table
)
SELECT 
    worker_id,
    MAX(CASE WHEN rn = 1 THEN month END) AS last_salary_date,
    MAX(CASE WHEN rn = 2 THEN month END) AS second_last_salary_date,
    MAX(CASE WHEN rn = 1 THEN basic_salary END) AS last_salary_amount
FROM salary_ranks
WHERE rn <= 2 -- 只保留最新和倒数第二的记录，减少计算量
GROUP BY worker_id;

方案二：自连接（直观易懂）

将最新薪资记录（rn=1）和倒数第二薪资记录（rn=2）通过worker_id关联，直接获取所需字段：

WITH salary_ranks AS (
    SELECT 
        worker_id,
        month,
        basic_salary,
        ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month DESC) AS rn
    FROM workers_table
)
SELECT 
    s1.worker_id,
    s1.month AS last_salary_date,
    s2.month AS second_last_salary_date,
    s1.basic_salary AS last_salary_amount
FROM salary_ranks s1
-- LEFT JOIN 兼容只有1条薪资记录的员工，此时second_last_salary_date会返回NULL
LEFT JOIN salary_ranks s2 
    ON s1.worker_id = s2.worker_id AND s2.rn = 2
WHERE s1.rn = 1;

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