SQL实现：将Decision列的逗号分隔值拆分为多行

针对你的需求——将表中Decision列的逗号分隔值拆分为单独行，并保留其余列的对应值，以下是不同主流数据库的实现方案：

MySQL（8.0及以上版本） #

方法1：递归CTE（兼容场景更广） #

假设你的表名为your_table，其余需要保留的列示例为id, col1, col2：

WITH RECURSIVE split_rows AS (
    SELECT 
        id, col1, col2,
        SUBSTRING_INDEX(Decision, ',', 1) AS split_decision,
        SUBSTRING(Decision, LENGTH(SUBSTRING_INDEX(Decision, ',', 1)) + 2) AS remaining_decisions
    FROM your_table
    WHERE Decision IS NOT NULL AND Decision != ''
    UNION ALL
    SELECT 
        id, col1, col2,
        SUBSTRING_INDEX(remaining_decisions, ',', 1) AS split_decision,
        SUBSTRING(remaining_decisions, LENGTH(SUBSTRING_INDEX(remaining_decisions, ',', 1)) + 2) AS remaining_decisions
    FROM split_rows
    WHERE remaining_decisions IS NOT NULL AND remaining_decisions != ''
)
SELECT id, col1, col2, split_decision AS Decision
FROM split_rows;

方法2：JSON_TABLE（更简洁） #

利用JSON函数快速拆分字符串：

SELECT 
    t.id, t.col1, t.col2, j.split_decision
FROM your_table t
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t.Decision, ',', '","'), '"]'),
    '$[*]' COLUMNS (split_decision VARCHAR(255) PATH '$')
) j
WHERE t.Decision IS NOT NULL AND t.Decision != '';

PostgreSQL #

使用string_to_array将字符串转为数组，再通过unnest展开为多行：

SELECT 
    id, col1, col2,
    unnest(string_to_array(Decision, ',')) AS Decision
FROM your_table
WHERE Decision IS NOT NULL AND Decision != '';

SQL Server（2016及以上版本） #

直接使用内置的STRING_SPLIT函数配合CROSS APPLY：

SELECT 
    t.id, t.col1, t.col2, s.value AS Decision
FROM your_table t
CROSS APPLY STRING_SPLIT(t.Decision, ',') s
WHERE t.Decision IS NOT NULL AND t.Decision != '';

额外注意事项 #

如果Decision列的值存在前后空格（比如'Product+availability , pricing'），可以在拆分后添加TRIM()函数去除空格，例如MySQL中改为TRIM(split_decision)，PostgreSQL中改为TRIM(unnest(string_to_array(Decision, ',')))。

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