求助：根据col3中Val1/Val2规则批量更新衍生列的SQL逻辑

需求说明 #

当col3列出现值Val1时，该行及后续所有行的衍生列值设为Val1，直到col3出现Val2；当col3出现Val2时，该行及后续所有行的衍生列值设为null，直到col3再次出现Val1。

核心实现思路 #

利用窗口函数（或变量，针对旧版本数据库）跟踪每行之前最近的状态触发值（Val1/Val2），再根据该触发值生成对应的衍生列。

支持窗口函数的数据库（PostgreSQL、MySQL 8.0+、SQL Server等） #

WITH status_markers AS (
    SELECT
        *,
        -- 提取当前行及之前最近的状态触发值
        MAX(CASE WHEN col3 IN ('Val1', 'Val2') THEN col3 END) 
            OVER (ORDER BY row_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_status
    FROM your_table
)
SELECT
    *,
    -- 根据最近状态生成衍生列
    CASE 
        WHEN latest_status = 'Val1' THEN 'Val1'
        WHEN latest_status = 'Val2' THEN NULL
        ELSE NULL -- 无初始状态时的默认值
    END AS derived_column
FROM status_markers
ORDER BY row_id;

不支持窗口函数的数据库（MySQL 5.x等） #

SELECT
    *,
    CASE 
        WHEN @latest_status = 'Val1' THEN 'Val1'
        WHEN @latest_status = 'Val2' THEN NULL
        ELSE NULL
    END AS derived_column,
    -- 逐行更新状态变量
    @latest_status := CASE 
        WHEN col3 IN ('Val1', 'Val2') THEN col3 
        ELSE @latest_status 
    END AS temp_status
FROM your_table,
     (SELECT @latest_status := NULL) AS init_var
ORDER BY row_id;

注意事项 #

• 必须确保表中有明确的排序字段（示例中用row_id，实际场景可以是时间戳、自增ID等），否则行顺序不确定会导致结果错误。
• 初始无状态（未出现过Val1/Val2）时，衍生列默认设为null，可根据需求调整默认值。

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