求编写HR数据库SQL查询：获取薪资与工作制变更记录（排除最新数据）

核心问题分析 #

你当前的代码会将薪资变更、工作制变更的记录分开输出，导致同一用户同一时点的变更拆成两行（一行仅含薪资，一行仅含工作制），无法满足“展示变更时点对应完整薪资与工作制”的需求。同时，用max(id)判断最新记录存在风险——若id未严格按变更日期递增，会导致筛选错误。

分步解决方案 #

1. 筛选排除最新记录：用窗口函数标记每个用户在两张表中的最新记录，排除这些记录后得到有效变更数据集。
2. 合并变更时点：将薪资变更日期、工作制变更日期去重合并，得到所有需要展示的变更时点。
3. 关联获取对应值：对每个变更时点，查询该用户在此时点生效的最新薪资与工作制，确保每条记录展示完整信息。

完整SQL示例（兼容多数关系型数据库） #

WITH filtered_salary AS (
    -- 筛选薪资表中排除每个用户最新记录的行
    SELECT 
        id_1,
        start_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY id_1 ORDER BY start_date DESC, id DESC) AS rn
    FROM tbuser_salary
),
filtered_working AS (
    -- 筛选工作制表中排除每个用户最新记录的行
    SELECT 
        id_1,
        effective_date,
        working_pattern,
        ROW_NUMBER() OVER (PARTITION BY id_1 ORDER BY effective_date DESC, id DESC) AS rn
    FROM tbuser_working_patterns
),
all_change_dates AS (
    -- 合并所有有效变更日期（去重同一用户同一时点的重复变更）
    SELECT id_1, start_date AS change_date FROM filtered_salary WHERE rn > 1
    UNION
    SELECT id_1, effective_date AS change_date FROM filtered_working WHERE rn > 1
)
-- 查询每个变更时点对应的薪资与工作制
SELECT 
    acd.id_1,
    acd.change_date,
    -- 获取变更时点生效的最新薪资
    (SELECT TOP 1 amount 
     FROM tbuser_salary s 
     WHERE s.id_1 = acd.id_1 AND s.start_date <= acd.change_date 
     ORDER BY s.start_date DESC, id DESC) AS amount,
    -- 获取变更时点生效的最新工作制
    (SELECT TOP 1 working_pattern 
     FROM tbuser_working_patterns wp 
     WHERE wp.id_1 = acd.id_1 AND wp.effective_date <= acd.change_date 
     ORDER BY wp.effective_date DESC, id DESC) AS working_pattern
FROM all_change_dates acd
ORDER BY acd.id_1, acd.change_date DESC;

优化说明（针对支持LATERAL JOIN的数据库如PostgreSQL、SQL Server） #

如果你的数据库支持LATERAL JOIN，可以用以下写法替代子查询，提升查询效率：

WITH filtered_salary AS (
    SELECT 
        id_1,
        start_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY id_1 ORDER BY start_date DESC, id DESC) AS rn
    FROM tbuser_salary
),
filtered_working AS (
    SELECT 
        id_1,
        effective_date,
        working_pattern,
        ROW_NUMBER() OVER (PARTITION BY id_1 ORDER BY effective_date DESC, id DESC) AS rn
    FROM tbuser_working_patterns
),
all_change_dates AS (
    SELECT id_1, start_date AS change_date FROM filtered_salary WHERE rn > 1
    UNION
    SELECT id_1, effective_date AS change_date FROM filtered_working WHERE rn > 1
)
SELECT 
    acd.id_1,
    acd.change_date,
    s.amount,
    wp.working_pattern
FROM all_change_dates acd
LEFT JOIN LATERAL (
    SELECT amount 
    FROM tbuser_salary s 
    WHERE s.id_1 = acd.id_1 AND s.start_date <= acd.change_date 
    ORDER BY s.start_date DESC, id DESC
    LIMIT 1
) s ON true
LEFT JOIN LATERAL (
    SELECT working_pattern 
    FROM tbuser_working_patterns wp 
    WHERE wp.id_1 = acd.id_1 AND wp.effective_date <= acd.change_date 
    ORDER BY wp.effective_date DESC, id DESC
    LIMIT 1
) wp ON true
ORDER BY acd.id_1, acd.change_date DESC;

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