SQL需求：添加日期差条件修改同一ID的Column A返回值

需求说明 #

现有同一ID的多条记录，所有记录的Column A当前均为'Yes'。需添加逻辑：按ID分组并按记录日期排序后，若最新记录与上一条记录的日期差≥90天，则将该最新记录的Column A改为'NO'。

示例数据：

解决方案 #

利用窗口函数LAG()获取上一条记录的日期，结合ROW_NUMBER()标记最新记录，再通过CASE WHEN实现字段值修改。以下是不同主流数据库的适配代码：

通用版（PostgreSQL等支持标准INTERVAL语法的数据库） #

SELECT
    ID,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY "Record date" DESC) = 1
             AND "Record date" - LAG("Record date") OVER (PARTITION BY ID ORDER BY "Record date") >= INTERVAL '90 days'
        THEN 'NO'
        ELSE "Column A"
    END AS "Column A",
    "Record date"
FROM
    your_table_name
ORDER BY
    ID, "Record date" DESC;

MySQL版 #

SELECT
    ID,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY `Record date` DESC) = 1
             AND DATEDIFF(`Record date`, LAG(`Record date`) OVER (PARTITION BY ID ORDER BY `Record date`)) >= 90
        THEN 'NO'
        ELSE `Column A`
    END AS `Column A`,
    `Record date`
FROM
    your_table_name
ORDER BY
    ID, `Record date` DESC;

SQL Server版 #

SELECT
    ID,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Record date] DESC) = 1
             AND DATEDIFF(day, LAG([Record date]) OVER (PARTITION BY ID ORDER BY [Record date]), [Record date]) >= 90
        THEN 'NO'
        ELSE [Column A]
    END AS [Column A],
    [Record date]
FROM
    your_table_name
ORDER BY
    ID, [Record date] DESC;

代码逻辑解释 #

• PARTITION BY ID：按ID分组处理每条记录，确保只对比同一ID下的记录
• ROW_NUMBER() OVER (...) = 1：标记每个ID下的最新记录（按日期降序排列，第一条即为最新）
• LAG("Record date") OVER (...)：获取当前记录的上一条（更早的）记录日期
• 日期差判断：通过对应数据库的日期函数，计算两条记录的天数差，满足≥90天则修改Column A值

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