能否通过SQL递归查询实现层级容器路径的拼接生成？

完全可以用SQL完成这个转换，而且递归查询（递归CTE）是非常合适的方案——尤其是你的traversal_ids[]已经存储了完整的层级ID序列，能大幅简化逻辑，甚至不需要复杂的递归就能实现。

核心思路 #

你的traversal_ids[]已经给出了每个容器从根到自身的完整ID链，核心步骤就是：

1. 拆分每个行的traversal_ids数组，得到每个层级的ID和对应的顺序位置
2. 关联原表，把每个ID映射为对应的name
3. 按容器ID分组，把name按层级顺序拼接成路径

示例SQL（以PostgreSQL为例，数组支持完善） #

无需递归的简化写法（推荐）

因为已经有完整的层级ID序列，直接拆分聚合即可：

SELECT 
    c.id,
    c.name,
    '/' || string_agg(curr.name, '/' ORDER BY pos) AS path
FROM containers c
-- 拆分traversal_ids数组，同时保留元素的顺序位置
JOIN LATERAL unnest(c.traversal_ids) WITH ORDINALITY AS t(node_id, pos) ON true
-- 关联原表获取每个ID对应的名称
JOIN containers curr ON t.node_id = curr.id
-- 按容器ID分组，拼接路径
GROUP BY c.id, c.name
ORDER BY c.id;

递归CTE写法（适用于更复杂的层级场景）

如果需要处理动态层级推导（比如没有现成的traversal_ids），递归CTE是标准方案，放到你的场景中也能生效：

WITH RECURSIVE container_paths AS (
    -- 初始化：拆分每个容器的层级ID序列
    SELECT 
        c.id AS parent_id,
        unnest(c.traversal_ids) AS node_id,
        generate_subscripts(c.traversal_ids, 1) AS pos
    FROM containers c
)
-- 聚合拼接路径
SELECT 
    c.id,
    c.name,
    '/' || string_agg(curr.name, '/' ORDER BY cp.pos) AS path
FROM containers c
JOIN container_paths cp ON c.id = cp.parent_id
JOIN containers curr ON cp.node_id = curr.id
GROUP BY c.id, c.name
ORDER BY c.id;

其他数据库适配（以MySQL 8.0+为例） #

MySQL中数组通常以JSON格式存储，用JSON_TABLE拆分：

SELECT 
    c.id,
    c.name,
    CONCAT('/', GROUP_CONCAT(curr.name ORDER BY j.pos SEPARATOR '/')) AS path
FROM containers c
-- 拆分JSON格式的traversal_ids数组
JOIN JSON_TABLE(
    c.traversal_ids,
    '$[*]' COLUMNS(
        node_id INT PATH '$',
        pos INT FOR ORDINALITY
    )
) j
JOIN containers curr ON j.node_id = curr.id
GROUP BY c.id, c.name
ORDER BY c.id;

总结 #

• 完全可以用SQL实现需求，甚至不需要递归就能完成（因为你已经有现成的层级ID序列）
• 递归查询是可行方案，更适合没有预存层级链的动态场景
• 不同数据库的语法细节略有差异，但核心逻辑一致：拆分级联ID→映射名称→按顺序拼接

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