SQL如何解析单列中以竖线分隔的多值数据

需求说明 #

将数据表中某列用竖线（|）分隔的多个值拆分成多行，同时保留其他列的对应数据。

不同数据库实现方案 #

MySQL（8.0及以上版本） #

借助JSON_TABLE函数实现拆分：

SELECT 
    t.id,
    t.name,
    j.category
FROM 
    your_table t
JOIN 
    JSON_TABLE(
        CONCAT('["', REPLACE(t.category, '|', '","'), '"]'),
        '$[*]' COLUMNS(category VARCHAR(255) PATH '$')
    ) j;

若使用MySQL 5.x版本（无JSON_TABLE），可通过递归CTE实现：

WITH RECURSIVE split_cte AS (
    SELECT 
        id,
        name,
        SUBSTRING_INDEX(category, '|', 1) AS category,
        SUBSTRING(category, LOCATE('|', category) + 1) AS remaining
    FROM your_table
    WHERE category IS NOT NULL AND category != ''
    UNION ALL
    SELECT 
        id,
        name,
        SUBSTRING_INDEX(remaining, '|', 1) AS category,
        SUBSTRING(remaining, LOCATE('|', remaining) + 1) AS remaining
    FROM split_cte
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT id, name, category FROM split_cte;

PostgreSQL #

使用unnest结合string_to_array函数：

SELECT 
    id,
    name,
    unnest(string_to_array(category, '|')) AS category
FROM your_table;

SQL Server（2016及以上版本） #

利用STRING_SPLIT函数：

SELECT 
    t.id,
    t.name,
    s.value AS category
FROM 
    your_table t
CROSS APPLY 
    STRING_SPLIT(t.category, '|') s;

Oracle #

通过CONNECT BY搭配正则表达式拆分：

SELECT 
    id,
    name,
    REGEXP_SUBSTR(category, '[^|]+', 1, LEVEL) AS category
FROM your_table
CONNECT BY 
    REGEXP_SUBSTR(category, '[^|]+', 1, LEVEL) IS NOT NULL
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL;

注意事项 #

• 将代码中的your_table替换为实际数据表名，category替换为需要拆分的列名
• 根据实际数据情况调整拆分后字段的类型和长度

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