如何根据同ID分组前置记录值更新表State字段的SQL实现问题

实现思路 #

你之前的写法问题在于仅用LAG取前1条记录判断，无法覆盖「前N条全是Save、或者中间存在非Save动作」的场景，正确逻辑需要先找到同ID分组下、当前记录之前最近的非Save动作对应的映射State，再按规则赋值：

• 非Save动作：直接取Map表中对应Action的State值
• Save动作：如果同ID分组内当前记录之前无任何非Save动作，赋值Draft；否则继承最近一个非Save动作的State

可运行SQL示例（通用支持窗口函数的数据库，如PostgreSQL/SQL Server/MySQL 8.0+） #

WITH state_calculation AS (
    SELECT 
        t.ID,
        t.Action,
        t.AddedOn,
        -- 先拿到非Save动作的映射State，Save动作此处先置为NULL
        m.State AS mapped_state,
        -- 按ID分组、时间排序，取最近的非空映射State（忽略NULL的Save行）
        LAST_VALUE(m.State) OVER (
            PARTITION BY t.ID 
            ORDER BY t.AddedOn
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            IGNORE NULLS
        ) AS last_non_save_state
    FROM your_table t
    LEFT JOIN Map m ON t.Action = m.Action
)
UPDATE your_table t
SET State = CASE
    WHEN t.Action = 'Save' THEN COALESCE(sc.last_non_save_state, 'Draft')
    ELSE sc.mapped_state
END
FROM state_calculation sc
WHERE t.ID = sc.ID 
  AND t.AddedOn = sc.AddedOn
  AND t.Action = sc.Action;

兼容不支持IGNORE NULLS的数据库适配方案 #

如果你的数据库不支持IGNORE NULLS语法（比如老版本MySQL），可以用分组标记法替换窗口函数逻辑：

WITH marked_data AS (
    SELECT 
        t.*,
        m.State AS mapped_state,
        -- 给同ID下遇到的非Save动作打分组标记，相同标记内的Save共享同一个非Save的State
        SUM(CASE WHEN m.State IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY t.ID 
            ORDER BY t.AddedOn
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS non_save_group
    FROM your_table t
    LEFT JOIN Map m ON t.Action = m.Action
),
group_state AS (
    SELECT 
        *,
        MAX(mapped_state) OVER (PARTITION BY ID, non_save_group) AS group_shared_state
    FROM marked_data
)
UPDATE your_table t
SET State = CASE
    WHEN t.Action = 'Save' THEN COALESCE(gs.group_shared_state, 'Draft')
    ELSE gs.mapped_state
END
FROM group_state gs
WHERE t.ID = gs.ID 
  AND t.AddedOn = gs.AddedOn
  AND t.Action = gs.Action;

验证说明 #

上述SQL可以完全匹配你提供的更新前后数据：

• ID为1a的前4条Save没有前置非Save动作，全部赋值Draft
• ID为2a的第2条Save前置最近非Save动作为Withdraw，继承为Withdrawn
• ID为3a的第3条Save前置最近非Save动作为Accept，继承为Accepted

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