如何将数据库中带分隔符的行值拆分至单独行？

原数据 #

目标结果 #

根据不同数据库类型，提供以下实现方案：

MySQL（8.0及以上版本） #

方法1：使用JSON_TABLE函数

SELECT 
    t.Name,
    j.ItemID
FROM 
    your_table t
JOIN 
    JSON_TABLE(
        CONCAT('["', REPLACE(t.ItemID, ',', '","'), '"]'),
        '$[*]' COLUMNS (ItemID VARCHAR(20) PATH '$')
    ) j;

方法2：递归CTE

WITH RECURSIVE split_cte AS (
    SELECT 
        Name,
        SUBSTRING_INDEX(ItemID, ',', 1) AS split_item,
        SUBSTRING(ItemID, LOCATE(',', ItemID) + 1) AS remaining_items
    FROM your_table
    WHERE ItemID IS NOT NULL AND ItemID != ''
    UNION ALL
    SELECT 
        Name,
        SUBSTRING_INDEX(remaining_items, ',', 1) AS split_item,
        SUBSTRING(remaining_items, LOCATE(',', remaining_items) + 1) AS remaining_items
    FROM split_cte
    WHERE remaining_items IS NOT NULL AND remaining_items != ''
)
SELECT Name, split_item AS ItemID
FROM split_cte;

PostgreSQL #

使用string_to_array搭配unnest函数：

SELECT 
    t.Name,
    unnest(string_to_array(t.ItemID, ',')) AS ItemID
FROM your_table t;

SQL Server（2016及以上版本） #

使用内置STRING_SPLIT函数：

SELECT 
    t.Name,
    s.value AS ItemID
FROM your_table t
CROSS APPLY STRING_SPLIT(t.ItemID, ',') s;

Oracle #

通过REGEXP_SUBSTR结合层级查询实现：

SELECT 
    t.Name,
    REGEXP_SUBSTR(t.ItemID, '[^,]+', 1, LEVEL) AS ItemID
FROM your_table t
CONNECT BY 
    REGEXP_SUBSTR(t.ItemID, '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR t.Name = t.Name
    AND PRIOR SYS_GUID() IS NOT NULL;

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