Oracle SQL如何将逗号分隔的角色编码替换为对应全称

实现思路 #

针对该场景，Oracle 11gR2及以上版本最优实现方案逻辑分为三步：

1. 拆分Employee表中逗号分隔的Role字段，将一行多编码拆分为多行单编码
2. 拆分后的单个编码和Role_master表左关联，匹配对应的角色全称
3. 按员工维度分组，将多个角色全称重新拼接为逗号分隔的字符串，保留空值行

实现SQL #

SELECT 
    e."S.No",
    e.Name,
    LISTAGG(rm."Role name", ',') WITHIN GROUP (ORDER BY INSTR(e.Role, rm.Role)) AS Role
FROM Employee e
LEFT JOIN (
    SELECT 
        "S.No",
        REGEXP_SUBSTR(Role, '[^,]+', 1, LEVEL) AS role_code
    FROM Employee
    WHERE Role IS NOT NULL AND Role <> ''
    CONNECT BY LEVEL <= REGEXP_COUNT(Role, ',') + 1
    AND PRIOR "S.No" = "S.No"
    AND PRIOR SYS_GUID() IS NOT NULL
) t ON e."S.No" = t."S.No"
LEFT JOIN Role_master rm ON t.role_code = rm.Role
GROUP BY e."S.No", e.Name
ORDER BY e."S.No";

关键说明 #

• 拆分逻辑里加PRIOR SYS_GUID() IS NOT NULL是为了避免层级查询时出现循环报错的问题
• 关联全程用左连接，保证Role为空的员工（示例中S.No=4的d）不会被过滤
• LISTAGG里的排序用INSTR(e.Role, rm.Role)是为了保证拼接后的角色顺序和原编码顺序完全一致
• 该方案全部用Oracle原生函数实现，无需自定义函数，性能最优、兼容性最好，适合生产环境使用

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