基于当前日期从日期生效行中识别“活跃”行

问题场景 #

员工信息表中，同一员工因工作条件变更会有多条带生效起始日期（Effective_Date）的记录，无生效结束日期；未来日期的记录代表计划中的变更。需要基于指定日期（如示例中的2022-09-16）找出当前"活跃"的记录——即生效日期早于等于指定日期，且下一条记录的生效日期晚于指定日期（若存在下一条）。

示例数据 #

解决方案 #

以下提供两种常用SQL实现方式：

方法1：使用LEAD()窗口函数获取下一条生效日期

通过LEAD()函数获取每条记录的下一条生效日期，再通过条件筛选出符合要求的活跃记录：

WITH employee_records AS (
    SELECT 
        Employee_ID,
        Effective_Date,
        Work_Status,
        Job_ID,
        -- 按员工分组、生效日期排序，获取下一条记录的生效日期
        LEAD(Effective_Date) OVER (PARTITION BY Employee_ID ORDER BY Effective_Date) AS Next_Effective_Date
    FROM your_table_name
)
SELECT 
    Employee_ID,
    Effective_Date,
    Work_Status,
    Job_ID
FROM employee_records
WHERE 
    Effective_Date <= '2022-09-16'
    -- 下一条日期不存在（当前是最新已生效记录）或下一条日期晚于指定日期
    AND (Next_Effective_Date IS NULL OR Next_Effective_Date > '2022-09-16');

方法2：使用ROW_NUMBER()筛选最新已生效记录

先过滤出所有生效日期≤指定日期的记录，再按员工分组取最新的那条：

WITH ranked_records AS (
    SELECT 
        Employee_ID,
        Effective_Date,
        Work_Status,
        Job_ID,
        -- 按员工分组，生效日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY Effective_Date DESC) AS rn
    FROM your_table_name
    WHERE Effective_Date <= '2022-09-16'
)
SELECT 
    Employee_ID,
    Effective_Date,
    Work_Status,
    Job_ID
FROM ranked_records
WHERE rn = 1;

示例结果 #

执行上述任一SQL，都会得到指定日期的活跃记录：

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