MariaDB：根据另一列的行值差异为指定列添加固定增量的条件更新问题

你的核心需求是：将连续相同p2的行归为一组，每组的p6值统一，且每组的p6比前一组大39。原SQL的问题在于两点：一是如果dt是字符串格式，直接排序会导致时间顺序混乱；二是逐行依赖前一行的p6值，虽然逻辑上接近，但在组内初始p6不一致或排序不稳定时，容易出现预期外的结果。更可靠的方式是先给连续的p2行分组，再基于组计算统一的p6值。

修正后的SQL方案 #

这里用CTE（MySQL 8.0+支持）先对连续的p2行分组，再计算每组的目标p6值，最后执行更新：

WITH grouped_data AS (
    SELECT
        id,
        p6 AS original_p6,
        -- 生成连续p2的组ID：当当前行p2与前一行不同时，组ID加1
        SUM(CASE WHEN LAG(p2) OVER (ORDER BY STR_TO_DATE(dt, '%d.%m.%Y %H:%i')) <> p2 THEN 1 ELSE 0 END) 
            OVER (ORDER BY STR_TO_DATE(dt, '%d.%m.%Y %H:%i')) AS group_id,
        -- 获取第一行的p6作为初始基准值
        FIRST_VALUE(p6) OVER (ORDER BY STR_TO_DATE(dt, '%d.%m.%Y %H:%i')) AS base_p6
    FROM mytable
),
group_target_p6 AS (
    SELECT
        group_id,
        base_p6 + (group_id * 39) AS target_p6
    FROM grouped_data
    GROUP BY group_id, base_p6
)
UPDATE mytable
JOIN grouped_data ON mytable.id = grouped_data.id
JOIN group_target_p6 ON grouped_data.group_id = group_target_p6.group_id
SET mytable.p6 = group_target_p6.target_p6
WHERE mytable.p6 <> group_target_p6.target_p6;

方案说明 #

1. 分组逻辑：
   • 用LAG(p2)获取前一行的p2值，判断当前行是否属于新组。
   • 用SUM()窗口函数累计新组的数量，生成唯一的group_id，确保连续相同p2的行拥有同一个group_id。
2. 目标p6计算：
   • 以表中第一行的p6为基准值（base_p6），每组的目标p6为base_p6 + group_id * 39：第一组（group_id=0）保持基准值，第二组（group_id=1）加39，第三组加78，以此类推。
3. 排序修正：
   • 用STR_TO_DATE(dt, '%d.%m.%Y %H:%i')将字符串格式的dt转换为日期时间类型，确保排序符合实际时间顺序，避免字符串排序的错误（比如1.3.2021会被错误排在11.2.2021前面）。

为什么原SQL没达到预期？ #

• 排序问题：如果你的dt是字符串格式，直接ORDER BY dt会按字典序排序，导致LAG()取到错误的前一行数据，最终计算出的p6值混乱。
• 逐行依赖的潜在问题：原SQL逐行计算new_p6，如果组内某行初始p6与组内其他行不一致，虽然最终会被更新为前一行的值，但基于组的方式更直接可靠，能确保整个组的p6完全统一，不会出现中间行的过渡值。

用这个方案测试你的示例数据，会得到你期望的结果：所有连续相同p2的行p6一致，每组依次递增39。

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