PostgreSQL中CTE递归查询结果的压缩转换实现方法咨询

要将递归CTE生成的层级表转换为包含id、parent_info、lit的目标表，核心是生成parent_info字段——即当前节点所有父节点的相关信息拼接串。最高效的方式是在递归CTE内部直接构建parent_info，后续只需筛选目标字段即可；也可以在CTE外部通过自连接+聚合函数实现。以下是具体实现：

方案1：递归过程中构建parent_info（推荐） #

这种方式不需要额外的连接操作，性能更优。在递归的每一层，将父节点的parent_info与父节点的标识信息（比如from+prod）拼接，最终每个节点都会携带完整的父路径信息。

PostgreSQL/SQL Server 示例 #

WITH recursive_data AS (
    -- 初始查询：选取根节点（无父节点），parent_info初始为空
    SELECT 
        id, 
        "from", 
        prod, 
        parent_id, 
        lit, 
        tip,
        CAST('' AS VARCHAR(1000)) AS parent_info
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归部分：拼接父节点的parent_info与父节点的标识
    SELECT 
        t.id, 
        t."from", 
        t.prod, 
        t.parent_id, 
        t.lit, 
        t.tip,
        CONCAT(
            rd.parent_info, 
            CASE WHEN rd.parent_info <> '' THEN ' > ' ELSE '' END, 
            CONCAT(rd."from", ':', rd.prod)
        ) AS parent_info
    FROM your_table t
    JOIN recursive_data rd ON t.parent_id = rd.id
)
-- 直接筛选目标字段
SELECT id, parent_info, lit
FROM recursive_data;

MySQL 示例 #

MySQL使用CONCAT_WS处理拼接更简洁，自动处理分隔符的空值情况：

WITH RECURSIVE recursive_data AS (
    SELECT 
        id, 
        `from`, 
        prod, 
        parent_id, 
        lit, 
        tip,
        '' AS parent_info
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        t.id, 
        t.`from`, 
        t.prod, 
        t.parent_id, 
        t.lit, 
        t.tip,
        CONCAT_WS(' > ', rd.parent_info, CONCAT(rd.`from`, ':', rd.prod)) AS parent_info
    FROM your_table t
    JOIN recursive_data rd ON t.parent_id = rd.id
)
SELECT id, parent_info, lit
FROM recursive_data;

方案2：CTE外部通过自连接+聚合生成parent_info #

如果无法在递归内部修改，可以通过自连接递归结果集，用字符串聚合函数拼接父节点信息：

PostgreSQL/SQL Server 示例 #

WITH recursive_data AS (
    -- 你的原有递归查询，需包含层级字段level
    SELECT id, "from", prod, parent_id, lit, tip, 1 AS level
    FROM your_table
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t."from", t.prod, t.parent_id, t.lit, t.tip, rd.level + 1
    FROM your_table t
    JOIN recursive_data rd ON t.parent_id = rd.id
)
SELECT 
    rd_child.id,
    -- 按层级顺序拼接当前节点的所有父节点信息
    STRING_AGG(CONCAT(rd_parent."from", ':', rd_parent.prod), ' > ') OVER (
        PARTITION BY rd_child.id 
        ORDER BY rd_parent.level 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS parent_info,
    rd_child.lit
FROM recursive_data rd_child
LEFT JOIN recursive_data rd_parent ON rd_child.id IN (SELECT id FROM recursive_data WHERE parent_id = rd_parent.id)
GROUP BY rd_child.id, rd_child.lit;

自定义parent_info格式 #

如果parent_info需要包含其他字段（比如父节点ID），只需修改拼接逻辑即可。例如要包含父节点ID：

-- 以PostgreSQL为例，修改递归部分的拼接
CONCAT(
    rd.parent_info, 
    CASE WHEN rd.parent_info <> '' THEN ' > ' ELSE '' END, 
    CONCAT('ID:', rd.id, ' (', rd."from", ':', rd.prod, ')')
) AS parent_info

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