MySQL同表递归查询并拼接列的实现需求及问题

现有表结构 #

id        |  parentid     | Name
1         |  NULL         | W
2         |  1            | O
3         |  1            | R
4         |  2            | L
5         |  4            | D

需求 #

递归追溯每条记录的parentid直至根节点（parentid为NULL），将途经的所有Name字段按层级顺序拼接为Code列，期望结果如下：

id        |  parentid     | Name | Code
1         |  NULL         | W    | W
2         |  1            | O    | W,O
3         |  1            | R    | W,R
4         |  2            | L    | W,O,L
5         |  4            | D    | W,O,L,D

错误尝试的SQL #

WITH RECURSIVE cte as 
( 
    SELECT  id, 
            id nextid, 
            parentid
    FROM    table
    UNION ALL
    SELECT  cte.id, 
            t1.id, 
            t1.parentid
    FROM    table
    JOIN    cte 
    ON      cte.parentid = p.id
)
SELECT Id, nextid RootId
FROM cte
WHERE parentid IS NULL

正确实现方案 #

递归CTE需要从每个节点向上追溯根节点，逐步拼接路径：

通用SQL（适配多数支持递归CTE的数据库） #

WITH RECURSIVE cte AS (
    -- 初始化：所有记录的初始Code为自身Name
    SELECT 
        id,
        parentid,
        Name,
        CAST(Name AS VARCHAR(255)) AS Code
    FROM your_table
    UNION ALL
    -- 递归：关联父节点，将父节点Name拼到当前Code前
    SELECT 
        c.id,
        t.parentid,
        c.Name,
        CONCAT(t.Name, ',', c.Code) AS Code
    FROM cte c
    JOIN your_table t ON c.parentid = t.id
)
-- 筛选每个节点最终的完整路径（已追溯到根节点）
SELECT id, parentid, Name, Code
FROM cte
WHERE parentid IS NULL
ORDER BY id;

不同数据库的细节调整 #

• MySQL：直接使用上述SQL即可，CONCAT函数支持多字符串拼接；
• PostgreSQL：可替换CONCAT为字符串连接运算符||，即t.Name || ',' || c.Code；
• SQL Server：字符串拼接可用CONCAT或+运算符，注意类型转换确保兼容性。

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