如何用递归CTE更新MySQL中首组连续3条reservation=0的记录？

可以使用递归Common Table Expressions（CTE）实现该需求，核心思路是通过递归CTE识别出连续的reservation=0的记录组，找到第一个包含至少3条记录的组，再更新该组的前3条记录。

具体实现步骤与代码 #

假设表名为bookings，执行以下SQL即可完成更新：

WITH RECURSIVE continuous_groups AS (
    -- 锚点成员：初始化第一条记录的分组信息
    SELECT 
        id,
        reservation,
        1 AS group_id,
        1 AS consecutive_count
    FROM bookings
    ORDER BY id
    LIMIT 1

    UNION ALL

    -- 递归成员：遍历后续记录，标记连续0值的分组
    SELECT 
        b.id,
        b.reservation,
        CASE 
            WHEN b.reservation = 0 AND cg.reservation = 0 THEN cg.group_id
            ELSE cg.group_id + 1
        END AS group_id,
        CASE 
            WHEN b.reservation = 0 AND cg.reservation = 0 THEN cg.consecutive_count + 1
            ELSE 1
        END AS consecutive_count
    FROM bookings b
    JOIN continuous_groups cg ON b.id = cg.id + 1
),
-- 筛选出第一个包含至少3条连续0值的分组
target_group AS (
    SELECT group_id
    FROM continuous_groups
    WHERE reservation = 0
    GROUP BY group_id
    HAVING COUNT(*) >= 3
    ORDER BY group_id
    LIMIT 1
)
-- 更新目标分组的前3条0值记录
UPDATE bookings b
JOIN (
    SELECT id
    FROM continuous_groups cg
    JOIN target_group tg ON cg.group_id = tg.group_id
    WHERE cg.reservation = 0
    ORDER BY cg.id
    LIMIT 3
) update_ids ON b.id = update_ids.id
SET b.reservation = 18;

代码说明 #

1. 递归CTE continuous_groups：

   • 锚点成员取出表中第一条记录，初始化分组ID和连续计数。
   • 递归成员通过关联上一条记录（假设id是连续自增的），判断当前记录是否和上一条同为reservation=0：如果是则归到同一分组，连续计数加1；否则新建分组，计数重置为1。

2. target_group CTE：

   • 统计每个全0分组的记录数，筛选出记录数≥3的分组，并取最早出现的那个。

3. 更新语句：

   • 关联目标分组的前3条0值记录，将它们的reservation字段更新为18。

注意事项 #

• 上述代码假设id是连续递增的整数，如果id存在间隙，需要用窗口函数ROW_NUMBER()生成连续序号来替代id进行递归关联。

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