如何在SQLite中通过递归连接与GROUP_CONCAT实现路径值分组拼接

要达成你需要的结果，核心是先定位所有路径的起始节点，再通过递归CTE遍历每条完整路径，最后分组聚合得到目标数据。具体实现如下：

核心思路 #

1. 筛选路径起点：路径的起始节点是那些从未被其他节点的successor_id指向的ID（即该ID不在所有非空successor_id的集合中）。
2. 递归遍历路径：以起点为锚点，递归拼接每条路径上的节点，记录每个节点所属的路径根ID、路径起止ID。
3. 分组聚合：按路径根ID分组，用GROUP_CONCAT按节点顺序拼接值，同时提取路径的最小/最大ID。

完整SQL代码 #

WITH RECURSIVE path_nodes AS (
    -- 锚点：选取所有路径的起始节点
    SELECT 
        id AS root_id,
        id,
        value,
        id AS min_id,
        id AS max_id
    FROM test
    WHERE id NOT IN (SELECT successor_id FROM test WHERE successor_id IS NOT NULL)
    
    UNION ALL
    
    -- 递归：遍历当前节点的后继节点
    SELECT 
        p.root_id,
        t.id,
        t.value,
        p.min_id,
        t.id AS max_id
    FROM path_nodes p
    JOIN test t ON p.id = t.successor_id
)
-- 分组聚合生成最终结果
SELECT 
    min_id,
    max_id,
    GROUP_CONCAT(value ORDER BY id) AS values
FROM path_nodes
GROUP BY root_id
ORDER BY min_id;

代码说明 #

• 锚点部分：通过子查询筛选出所有起始节点（示例中的1、7、12），为每个节点初始化root_id（标记所属路径）、min_id和max_id（初始为当前节点ID）。
• 递归部分：通过p.id = t.successor_id关联当前节点的后继，继承root_id和min_id，并将max_id更新为后继节点的ID，实现路径的延伸。
• 聚合部分：按root_id分组，GROUP_CONCAT(value ORDER BY id)保证值的拼接顺序与路径节点顺序一致，同时直接使用递归过程中记录的min_id和max_id作为路径的起止ID。

执行上述代码后，将得到你预期的结果：

min_id  max_id    values
------  ------  --------
     1       4  v1,v2,v3
     7       9  v4,v5
    12      12  v6

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