SQL分组查询列值替换：同CARD_NBR记录END_DATE计算实现方法

解决方法 #

你这个需求适合用窗口函数LEAD()实现，GROUP BY是分组聚合函数，会将同组多行合并为一行，不符合保留所有原始记录的要求，因此无法得到预期结果。

LEAD() 可以直接取同分组内当前行的下一行指定字段值，刚好匹配你的需求，通用SQL写法如下：

SELECT 
    CARD_NBR,
    SERIEL_NO,
    EFFECTIVE_DATE,
    -- 下一行生效日期减1天，没有下一行则填充9999-12-31
    COALESCE(DATE_SUB(LEAD(EFFECTIVE_DATE) OVER (
        PARTITION BY CARD_NBR 
        ORDER BY SERIEL_NO
    ), INTERVAL 1 DAY), '9999-12-31') AS END_DATE
FROM 你的表名;

逻辑说明

• PARTITION BY CARD_NBR：按卡号分组，每个卡号的记录单独计算
• ORDER BY SERIEL_NO：组内按流水号升序排序，保证取到的下一行是时间更晚的记录，也可以替换为ORDER BY EFFECTIVE_DATE，结果一致
• LEAD(EFFECTIVE_DATE)：取同组内当前行下一行的生效日期值，组内最后一行返回NULL
• DATE_SUB(xxx, INTERVAL 1 DAY)：将下一行的生效日期减1天作为当前行的结束日期
• COALESCE(xxx, '9999-12-31')：将最后一行的NULL值替换为固定结束日期9999-12-31

不同数据库兼容调整

如果使用非MySQL类数据库，只需要调整日期减1的语法即可：

• SQL Server：将DATE_SUB(日期, INTERVAL 1 DAY)替换为DATEADD(day, -1, 日期)
• Oracle、PostgreSQL：直接写日期 - 1即可

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