技术问询：按CardNo分组，将上一行End_Date设为不同insured id行的BirthDate

嘿，这个需求用SQL的窗口函数就能轻松搞定！我分查询验证逻辑和实际数据更新两种场景给你详细说明，你可以根据自己的情况选用~

首先我们可以先写查询语句，看看处理后的结果是否符合预期，再执行更新操作更稳妥。这里核心用LAG()窗口函数——它能帮我们在同一个分组内获取上一行的指定字段值。

SELECT 
    CardNo,
    insured_id,
    BirthDate AS original_BirthDate,
    End_Date,
    -- 拿到同CardNo分组内上一行的End_Date
    LAG(End_Date) OVER (PARTITION BY CardNo ORDER BY [你的排序字段]) AS prev_row_EndDate,
    -- 当当前行insured_id和上一行不同时，用上一行End_Date替换BirthDate，否则保留原值
    CASE 
        WHEN insured_id != LAG(insured_id) OVER (PARTITION BY CardNo ORDER BY [你的排序字段])
        THEN LAG(End_Date) OVER (PARTITION BY CardNo ORDER BY [你的排序字段])
        ELSE BirthDate
    END AS updated_BirthDate
FROM 
    你的数据表名;

⚠️ 重要提示：

• PARTITION BY CardNo是按卡号分组，确保我们只在同一个卡号的范围内找“上一行”；
• ORDER BY [你的排序字段]必须替换成你实际用来确定行顺序的字段（比如保单生效时间Start_Date、记录创建时间CreateTime等），不然“上一行”的定义会混乱，结果肯定不对！

如果查询结果没问题，就可以执行更新操作了。不同数据库的更新语法略有差异，我给你列几种常用的：

针对SQL Server/PostgreSQL（支持CTE+窗口函数） #

WITH processed_data AS (
    SELECT 
        主键字段, -- 替换成你表的主键，比如ID
        CardNo,
        insured_id,
        BirthDate,
        LAG(End_Date) OVER (PARTITION BY CardNo ORDER BY [你的排序字段]) AS prev_row_EndDate,
        LAG(insured_id) OVER (PARTITION BY CardNo ORDER BY [你的排序字段]) AS prev_insured_id
    FROM 
        你的数据表名
)
UPDATE processed_data
SET BirthDate = prev_row_EndDate
WHERE insured_id != prev_insured_id;

针对MySQL 8.0+（支持窗口函数） #

UPDATE 你的数据表名 t1
JOIN (
    SELECT 
        主键字段,
        LAG(End_Date) OVER (PARTITION BY CardNo ORDER BY [你的排序字段]) AS prev_row_EndDate,
        LAG(insured_id) OVER (PARTITION BY CardNo ORDER BY [你的排序字段]) AS prev_insured_id
    FROM 你的数据表名
) t2 ON t1.主键字段 = t2.主键字段
SET t1.BirthDate = t2.prev_row_EndDate
WHERE t1.insured_id != t2.prev_insured_id;

针对MySQL 5.x（不支持窗口函数，用变量实现） #

如果你的MySQL版本比较旧，没法用窗口函数，可以借助用户变量来实现：

SET @prev_card = '', @prev_end = '', @prev_insured = '';

UPDATE 你的数据表名
SET BirthDate = CASE 
    WHEN CardNo = @prev_card AND insured_id != @prev_insured THEN @prev_end
    ELSE BirthDate
END,
-- 更新变量，为下一行做准备
@prev_end = End_Date,
@prev_insured = insured_id,
@prev_card = CardNo
ORDER BY CardNo, [你的排序字段];

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