基于共享ID合并同一grp内关联sub_grp的SQL实现需求

这是一个经典的连通分量合并场景——我们需要在每个grp分组内，把所有共享至少一个id的sub_grp合并为同一个组标识。下面是基于MySQL的完整实现方案，利用递归CTE（公共表表达式）来处理这种关联关系：

1. 测试数据准备 #

首先创建并插入测试数据：

CREATE TABLE test_data (
    grp INT,
    id VARCHAR(10),
    sub_grp INT
);

INSERT INTO test_data VALUES
(10, 'A2', 1),
(10, 'B4', 2),
(10, 'F1', 2),
(10, 'B3', 3),
(10, 'C2', 4),
(10, 'A2', 4),
(10, 'H4', 5),
(10, 'K0', 5),
(10, 'Z3', 5),
(10, 'F1', 5),
(10, 'A1', 5),
(10, 'A', 6),
(10, 'B', 6),
(10, 'B', 7),
(10, 'C', 7),
(10, 'C', 8),
(10, 'D', 8),
(20, 'A', 1),
(20, 'B', 1),
(20, 'B', 2),
(20, 'C', 2),
(20, 'C', 3),
(20, 'D', 3);

2. 核心SQL查询 #

使用递归CTE来合并连通的子组：

WITH RECURSIVE cte AS (
    -- 初始步骤：为每个sub_grp建立基础关联，记录每个id对应的最小sub_grp作为初始组标识
    SELECT 
        grp,
        sub_grp AS original_sub_grp,
        sub_grp AS current_sub_grp,
        MIN(sub_grp) OVER (PARTITION BY grp, id) AS min_sub_grp
    FROM test_data
    UNION ALL
    -- 递归步骤：通过共享id关联不同sub_grp，不断更新组标识为更小的值，避免循环
    SELECT 
        c.grp,
        c.original_sub_grp,
        t2.sub_grp,
        LEAST(c.min_sub_grp, t2.min_sub_grp)
    FROM cte c
    JOIN test_data t 
        ON c.grp = t.grp 
        AND c.current_sub_grp = t.sub_grp
    JOIN test_data t2
        ON t.grp = t2.grp 
        AND t.id = t2.id
    WHERE t2.sub_grp != c.current_sub_grp
        AND t2.sub_grp < c.min_sub_grp
)
-- 最终聚合：为每条原始记录找到所属合并组的最小标识
SELECT 
    td.grp,
    td.id,
    MIN(c.min_sub_grp) AS sub_grp
FROM test_data td
JOIN cte c 
    ON td.grp = c.grp 
    AND td.sub_grp = c.original_sub_grp
GROUP BY td.grp, td.id, td.sub_grp
ORDER BY td.grp, td.sub_grp, td.id;

3. 逻辑说明 #

• 初始阶段：我们为每条记录标记其原始sub_grp，同时计算该id在当前grp下的最小sub_grp，作为初始的组标识。
• 递归阶段：通过共享的id关联不同的sub_grp，将组标识更新为关联子组中的最小值，确保所有连通的子组最终会合并到同一个最小标识下，同时通过t2.sub_grp < c.min_sub_grp避免递归循环。
• 最终聚合：为每条原始记录找到其所属合并组的最小标识，得到预期结果。

4. 预期结果 #

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