Oracle SQL实现单行国家编码数据拆分为多行展示

嘿，这个需求我之前做项目的时候碰到过，用Oracle SQL完全可以搞定，给你分享两个常用的实现方式，按需选就行：

方法一：使用CONNECT BY + 正则函数 #

这种方法兼容性比较好，适合各个版本的Oracle：

假设你的源表叫country_codes，包含两个字段：country_name（存储USA、Canada这类国家名称）和code_list（存储逗号分隔的编码字符串，比如'23,24,25,30'），可以用下面的SQL实现拆分：

SELECT 
    country_name,
    TRIM(REGEXP_SUBSTR(code_list, '[^,]+', 1, LEVEL)) AS country_code
FROM 
    country_codes
CONNECT BY 
    LEVEL <= REGEXP_COUNT(code_list, '[^,]+')
    AND PRIOR country_name = country_name
    AND PRIOR SYS_GUID() IS NOT NULL;

关键部分解释： #

• REGEXP_SUBSTR(code_list, '[^,]+', 1, LEVEL)：按逗号拆分字符串，LEVEL表示取第几个拆分后的元素
• REGEXP_COUNT(code_list, '[^,]+')：计算编码的总个数，用来控制生成的行数
• PRIOR SYS_GUID() IS NOT NULL：这步很重要，用来避免多国家记录时出现笛卡尔积，防止生成重复数据

方法二：使用XMLTABLE（Oracle 11gR2及以上） #

如果你的Oracle版本是11gR2或更高，用XMLTABLE的写法会更简洁：

SELECT 
    cc.country_name,
    TRIM(x.code) AS country_code
FROM 
    country_codes cc,
    XMLTABLE(
        'tokenize(., ",")'
        PASSING cc.code_list
        COLUMNS code VARCHAR2(10) PATH '.'
    ) x;

关键部分解释： #

• XMLTABLE结合tokenize函数：直接把逗号分隔的字符串转换成行集
• 关联原表后就能直接得到每行一个国家+编码的结果，代码更清爽

小提示 #

如果你的code_list里有多余的空格，记得用TRIM函数处理拆分后的编码，避免出现带空格的无效数据哦。

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