如何在SQL中拆分含数组格式日期与用户ID的多行数据？

需要将每行中存储为数组格式的DATES和集合格式的USER_ID拆分为一一对应的行，实现每个日期匹配所有用户ID的笛卡尔积效果。以下是不同主流数据库的解决方案：

MySQL 8.0+ 实现代码 #

SELECT 
    t.TYPE,
    DATE_FORMAT(jd.date_val, '%m/%d/%Y') AS DATES,
    ju.user_id AS USER_ID
FROM 
    your_table t
-- 拆分日期数组
CROSS JOIN JSON_TABLE(
    t.DATES,
    '$[*]' COLUMNS(date_val DATE PATH '$')
) jd
-- 转换用户ID格式并拆分
CROSS JOIN JSON_TABLE(
    REPLACE(REPLACE(t.USER_ID, '{', '['), '}', ']'),
    '$[*]' COLUMNS(user_id INT PATH '$')
) ju
ORDER BY 
    t.TYPE DESC, jd.date_val, ju.user_id;

关键说明 #

• JSON_TABLE：将JSON数组拆分为多行记录；
• REPLACE函数：把用户ID的{...}集合格式转换为JSON数组[...]，让JSON_TABLE可以解析；
• DATE_FORMAT：将日期转换为目标格式mm/dd/yyyy；
• CROSS JOIN：实现日期与用户ID的全量匹配，生成笛卡尔积。

PostgreSQL 实现代码 #

SELECT 
    t.TYPE,
    TO_CHAR(jd.date_val::DATE, 'MM/DD/YYYY') AS DATES,
    ju.user_id::INT AS USER_ID
FROM 
    your_table t
-- 拆分日期数组
CROSS JOIN jsonb_array_elements_text(t.DATES::JSONB) AS jd(date_val)
-- 转换用户ID格式并拆分
CROSS JOIN jsonb_array_elements_text(REPLACE(REPLACE(t.USER_ID, '{', '['), '}', ']')::JSONB) AS ju(user_id)
ORDER BY 
    t.TYPE DESC, jd.date_val::DATE, ju.user_id::INT;

关键说明 #

• ::JSONB：将字符串类型的日期/用户ID转换为JSONB类型；
• jsonb_array_elements_text：拆分JSON数组为单行文本记录；
• TO_CHAR：完成日期格式的转换。

SQL Server 实现代码 #

SELECT 
    t.TYPE,
    FORMAT(CAST(jd.date_val AS DATE), 'MM/dd/yyyy') AS DATES,
    ju.user_id AS USER_ID
FROM 
    your_table t
-- 拆分日期数组
CROSS JOIN OPENJSON(t.DATES)
    WITH (date_val DATE '$') jd
-- 转换用户ID格式并拆分
CROSS JOIN OPENJSON(REPLACE(REPLACE(t.USER_ID, '{', '['), '}', ']'))
    WITH (user_id INT '$') ju
ORDER BY 
    CASE t.TYPE WHEN 'LEAVE' THEN 1 WHEN 'WORK' THEN 2 END,
    CAST(jd.date_val AS DATE),
    ju.user_id;

关键说明 #

• OPENJSON：解析JSON数组并返回结构化行数据；
• FORMAT：将日期转换为目标格式；
• CASE语句：用于排序，让LEAVE类型的记录排在WORK之前，与期望结果一致。

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