MySQL需求：将同列连续值更新为对应序列的最大值

这个需求本质是把连续递增1的数值簇统一替换成簇内的最大值，用普通JOIN确实很难处理这种递归关联的情况，这里用MySQL的递归CTE（Common Table Expression）就能完美解决，具体步骤如下：

先明确数据情况 #

原始表数据（假设表名为your_table）：

ID | value
---|------
1  | 4
2  | 5
3  | 10
4  | 6
5  | 12
6  | 11
7  | 18

期望最终结果：

ID | value
---|------
1  | 6
2  | 6
3  | 12
4  | 6
5  | 12
6  | 12
7  | 18

解决方案：递归CTE + 更新操作 #

步骤1：用递归CTE定位每个值所属簇的最大值 #

递归CTE可以帮我们追踪每个值能关联到的最大连续值，核心思路是：

• 先锚定每个值的初始最大组值为自身
• 递归查找当前值+1的存在记录，不断更新组内最大值，直到找不到连续值为止

代码如下：

WITH RECURSIVE value_groups AS (
    -- 锚点成员：初始化每个值的组最大值为自身
    SELECT value AS original_value, value AS max_group_value
    FROM your_table
    UNION ALL
    -- 递归成员：寻找当前组最大值+1的记录，更新组最大值
    SELECT vg.original_value, yt.value
    FROM value_groups vg
    JOIN your_table yt ON yt.value = vg.max_group_value + 1
)
-- 提取每个原始值对应的最终组最大值
SELECT original_value, MAX(max_group_value) AS target_value
FROM value_groups
GROUP BY original_value;

执行这个查询会得到每个原始值对应的目标替换值：

original_value | target_value
---------------|-------------
4              | 6
5              | 6
6              | 6
10             | 12
11             | 12
12             | 12
18             | 18

步骤2：基于上述结果更新原表 #

把上面的查询作为子查询，和原表关联后执行更新：

WITH RECURSIVE value_groups AS (
    SELECT value AS original_value, value AS max_group_value
    FROM your_table
    UNION ALL
    SELECT vg.original_value, yt.value
    FROM value_groups vg
    JOIN your_table yt ON yt.value = vg.max_group_value + 1
),
group_max AS (
    SELECT original_value, MAX(max_group_value) AS target_value
    FROM value_groups
    GROUP BY original_value
)
UPDATE your_table t
JOIN group_max gm ON t.value = gm.original_value
SET t.value = gm.target_value;

执行完这条语句后，你的表数据就会变成期望的结果。

补充说明 #

• 该方案适用于MySQL 8.0及以上版本（递归CTE是8.0版本引入的特性）
• 如果是低于8.0的版本，需要用存储过程循环处理关联值，但递归CTE是最简洁高效的实现方式

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