MySQL中按顺序更新表行的实现方案咨询

首先，创建表的SQL语句如下：

CREATE TABLE enrollments 
(
    person_id INT,
    date DATE,
    duration int
);

INSERT INTO enrollments (person_id, date, duration)
VALUES (1, '2004-01-01', 35),
       (1, '2004-01-15', 60),
       (1, '2004-02-01', 15),
       (1, '2004-09-01', 30),
       (2, '1994-01-18', 30),
       (2, '1994-03-01', 30);

表中初始数据如下：

需要实现的逻辑：
按person_id分组，对每组内的记录按原始date排序后逐行调整日期：

• 组内第一行保持原日期不变
• 从第二行开始，计算前一行调整后的日期 + 前一行的duration天数，将结果与当前行原始日期对比：
  • 若计算结果 > 当前行原始日期，将当前行日期更新为该计算结果
  • 若计算结果 ≤ 当前行原始日期，保持当前行原始日期不变

以person_id=1为例：

1. 第一行：2004-01-01（无前置行，保持原日期）
2. 第二行：计算2004-01-01 + 35天 = 2004-02-05，因2004-02-05 > 2004-01-15，更新日期为2004-02-05
3. 第三行：计算2004-02-05 + 60天 = 2004-04-05，因2004-04-05 > 2004-02-01，更新日期为2004-04-05
4. 第四行：计算2004-04-05 + 15天 = 2004-04-20，因2004-04-20 < 2004-09-01，保持原日期2004-09-01

这个需求属于递归依赖场景（每一行的调整结果依赖前一行的最终值），可以通过**递归CTE（Common Table Expression）**实现，具体SQL代码如下：

WITH ranked_enrollments AS (
    -- 给每个person_id的记录按原始date排序，生成行号
    SELECT 
        person_id,
        date,
        duration,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date) AS row_num
    FROM enrollments
),
recursive_adjustment AS (
    -- 递归基础：每组第一行，调整后日期为原日期
    SELECT 
        person_id,
        date AS original_date,
        date AS adjusted_date,
        duration,
        row_num
    FROM ranked_enrollments
    WHERE row_num = 1

    UNION ALL

    -- 递归计算：逐行生成调整后日期
    SELECT 
        re.person_id,
        re.date AS original_date,
        -- 取前一行调整后日期+时长、当前行原日期的较大值
        GREATEST(DATE_ADD(ra.adjusted_date, INTERVAL ra.duration DAY), re.date) AS adjusted_date,
        re.duration,
        re.row_num
    FROM ranked_enrollments re
    JOIN recursive_adjustment ra 
        ON re.person_id = ra.person_id 
        AND re.row_num = ra.row_num + 1
)
-- 输出最终结果
SELECT 
    person_id,
    adjusted_date AS date,
    duration
FROM recursive_adjustment
ORDER BY person_id, row_num;

执行后得到的结果：

直接更新原表（MySQL为例） #

如果需要修改enrollments表中的date字段，可使用以下语句：

WITH ranked_enrollments AS (
    SELECT 
        person_id,
        date,
        duration,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date) AS row_num
    FROM enrollments
),
recursive_adjustment AS (
    SELECT 
        person_id,
        date AS original_date,
        date AS adjusted_date,
        duration,
        row_num
    FROM ranked_enrollments
    WHERE row_num = 1

    UNION ALL

    SELECT 
        re.person_id,
        re.date AS original_date,
        GREATEST(DATE_ADD(ra.adjusted_date, INTERVAL ra.duration DAY), re.date) AS adjusted_date,
        re.duration,
        re.row_num
    FROM ranked_enrollments re
    JOIN recursive_adjustment ra 
        ON re.person_id = ra.person_id 
        AND re.row_num = ra.row_num + 1
)
UPDATE enrollments e
JOIN recursive_adjustment ra 
    ON e.person_id = ra.person_id 
    AND e.date = ra.original_date 
    AND e.duration = ra.duration
SET e.date = ra.adjusted_date;

注意：若同一person_id存在重复的date和duration记录，建议添加自增主键作为唯一关联键，避免错误更新。

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