如何将表中Location字段的ID字符串转换为对应名称字符串？

问题核心是regexp_split_to_table默认不保留拆分元素的顺序，导致无法正确拼接层级路径。通过带序数的拆分语法可以解决这个问题，以下是完整的实现方案：

修正后的SQL查询 #

WITH location_parts AS (
    SELECT
        col.id AS col_id,
        col.name AS col_name,
        col.location AS col_full_loc,
        -- 拆分Location时同时获取每个片段的顺序序号
        parts.part AS loc_id_str,
        parts.ordinality AS part_order
    FROM collection col
    CROSS JOIN regexp_split_to_table(col.location, '/') WITH ORDINALITY AS parts(part, ordinality)
    -- 过滤开头/拆分出的空字符串
    WHERE parts.part <> ''
),
name_mapped AS (
    SELECT
        lp.col_id,
        lp.col_name,
        lp.col_full_loc,
        -- 将字符串ID转为整数，关联原表获取对应名称
        c.name AS part_name,
        lp.part_order
    FROM location_parts lp
    LEFT JOIN collection c ON lp.loc_id_str::integer = c.id
)
SELECT
    col_id AS ID,
    col_name AS Name,
    col_full_loc AS Location,
    -- 按顺序拼接名称，处理空路径场景
    CASE 
        WHEN COUNT(part_name) = 0 THEN '/'
        ELSE '/' || string_agg(part_name, '/' ORDER BY part_order)
    END AS FullName
FROM name_mapped
GROUP BY col_id, col_name, col_full_loc
ORDER BY col_id;

关键逻辑说明 #

• WITH ORDINALITY语法：给拆分后的每个片段添加一个顺序序号，彻底解决拆分后元素顺序丢失的问题，这是核心修复点。
• 空片段过滤：由于Location以/开头，拆分后第一个元素是空字符串，必须过滤避免后续ID转换报错。
• 有序拼接：使用string_agg(..., ORDER BY part_order)确保按原始路径的层级顺序拼接名称，生成正确的FullName。
• 空路径兼容：当Location为/时，通过CASE语句直接返回/，保持结果一致性。

执行该查询后，会得到你期望的结果：

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