如何将SQL表多值字段拆分并生成笛卡尔积式多行数据？

要实现将表中逗号分隔的Colour和Shape列拆分成笛卡尔积形式的多行，核心思路是分别拆分两个多值列，再通过Name字段关联两个拆分结果集，得到所有可能的组合。以下是主流SQL方言的具体实现：

SQL Server（2016+版本） #

利用内置的STRING_SPLIT函数拆分字符串，同时处理空值和多余空格：

WITH SplitColours AS (
    SELECT 
        Name,
        TRIM(value) AS Colour
    FROM YourTable
    CROSS APPLY STRING_SPLIT(Colour, ',')
),
SplitShapes AS (
    SELECT 
        Name,
        TRIM(value) AS Shape
    FROM YourTable
    -- 处理Shape为空的情况，确保生成一行空值记录
    CROSS APPLY STRING_SPLIT(ISNULL(Shape, ''), ',')
)
SELECT 
    sc.Name,
    sc.Colour,
    ss.Shape
FROM SplitColours sc
JOIN SplitShapes ss ON sc.Name = ss.Name
ORDER BY sc.Name, sc.Colour, ss.Shape;

MySQL #

MySQL没有内置字符串拆分函数，需要借助递归生成数字辅助表，结合SUBSTRING_INDEX实现拆分：

WITH RECURSIVE nums AS (
    -- 生成1-10的数字表（可根据实际多值数量调整上限）
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
),
SplitColours AS (
    SELECT
        Name,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Colour, ',', n), ',', -1)) AS Colour
    FROM YourTable
    JOIN nums ON n <= LENGTH(Colour) - LENGTH(REPLACE(Colour, ',', '')) + 1
    WHERE Colour IS NOT NULL AND Colour != ''
    -- 补充Colour为空的记录（如果有）
    UNION ALL
    SELECT Name, Colour FROM YourTable WHERE Colour IS NULL OR Colour = ''
),
SplitShapes AS (
    SELECT
        Name,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Shape, ',', n), ',', -1)) AS Shape
    FROM YourTable
    JOIN nums ON n <= LENGTH(Shape) - LENGTH(REPLACE(Shape, ',', '')) + 1
    WHERE Shape IS NOT NULL AND Shape != ''
    -- 补充Shape为空的记录
    UNION ALL
    SELECT Name, Shape FROM YourTable WHERE Shape IS NULL OR Shape = ''
)
SELECT
    sc.Name,
    sc.Colour,
    ss.Shape
FROM SplitColours sc
JOIN SplitShapes ss ON sc.Name = ss.Name
ORDER BY sc.Name, sc.Colour, ss.Shape;

PostgreSQL #

使用STRING_TO_ARRAY将字符串转为数组，再用UNNEST展开数组：

WITH SplitColours AS (
    SELECT
        Name,
        TRIM(unnest(string_to_array(Colour, ','))) AS Colour
    FROM YourTable
),
SplitShapes AS (
    SELECT
        Name,
        TRIM(unnest(string_to_array(COALESCE(Shape, ''), ','))) AS Shape
    FROM YourTable
)
SELECT
    sc.Name,
    sc.Colour,
    ss.Shape
FROM SplitColours sc
JOIN SplitShapes ss ON sc.Name = ss.Name
ORDER BY sc.Name, sc.Colour, ss.Shape;

关键注意点 #

• 使用TRIM去除每个拆分值前后的空格，避免结果出现多余空格
• 处理空值/空字符串的情况，确保单值列（如Phil的Shape）能生成对应行
• 拆分后的两个结果集通过Name关联，得到所有Colour和Shape的组合

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