如何用SQL实现单单元格多值拆分与笛卡尔积关联？

当然可以实现这个需求，核心思路是先把每个单元格里的多值拆分成独立行，再通过交叉连接（笛卡尔积）将日期和用户一一组合。以下是主流数据库的具体实现方案：

前提说明 #

原数据表中单元格的内容带双引号和逗号分隔，第一步需要先清理这些格式字符，再拆分。

MySQL 8.0+ 实现 #

WITH split_dates AS (
    SELECT TRIM(BOTH '"' FROM value) AS column1
    FROM your_table,
         STRING_SPLIT(TRIM(BOTH '"' FROM column1), '", "')
),
split_users AS (
    SELECT TRIM(BOTH '"' FROM value) AS column2
    FROM your_table,
         STRING_SPLIT(TRIM(BOTH '"' FROM column2), '", "')
)
SELECT column1, column2
FROM split_dates
CROSS JOIN split_users;

如果你的MySQL版本低于8.0.19（不支持STRING_SPLIT），可以用JSON_TABLE替代：

WITH split_dates AS (
    SELECT j.column1
    FROM your_table,
         JSON_TABLE(
             REPLACE(REPLACE(column1, '"', '\\"'), ', ', '","'),
             '$[*]' COLUMNS(column1 VARCHAR(20) PATH '$')
         ) j
),
split_users AS (
    SELECT j.column2
    FROM your_table,
         JSON_TABLE(
             REPLACE(REPLACE(column2, '"', '\\"'), ', ', '","'),
             '$[*]' COLUMNS(column2 VARCHAR(20) PATH '$')
         ) j
)
SELECT column1, column2
FROM split_dates
CROSS JOIN split_users;

PostgreSQL 实现 #

WITH split_dates AS (
    SELECT TRIM(BOTH '"' FROM unnest(string_to_array(column1, '", "'))) AS column1
    FROM your_table
),
split_users AS (
    SELECT TRIM(BOTH '"' FROM unnest(string_to_array(column2, '", "'))) AS column2
    FROM your_table
)
SELECT column1, column2
FROM split_dates
CROSS JOIN split_users;

SQL Server 实现 #

WITH split_dates AS (
    SELECT TRIM(BOTH '"' FROM value) AS column1
    FROM your_table
    CROSS APPLY STRING_SPLIT(TRIM(BOTH '"' FROM column1), '", "')
),
split_users AS (
    SELECT TRIM(BOTH '"' FROM value) AS column2
    FROM your_table
    CROSS APPLY STRING_SPLIT(TRIM(BOTH '"' FROM column2), '", "')
)
SELECT column1, column2
FROM split_dates
CROSS JOIN split_users;

关键步骤解释 #

1. 清理字符串：用TRIM(BOTH '"' ...)去掉首尾的双引号，拆分时用", "作为分隔符，避免拆分后每个值带多余引号。
2. 拆分多值为行：通过数据库内置的字符串拆分函数（如STRING_SPLIT、unnest+string_to_array）将单个单元格的多值拆分成独立行。
3. 交叉连接：将拆分后的日期表和用户表做交叉连接，得到每个日期对应所有用户的组合。

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