如何编写SQL实现多表关联并拆分service列的多值数据？

核心思路是先完成三表关联得到包含service列的数据集，再对该列的逗号分隔值进行拆分，将每个值转为单独行。以下分主流数据库给出具体实现：

MySQL 8.0+（推荐用JSON_TABLE） #

利用JSON函数将逗号分隔字符串转为JSON数组，再通过JSON_TABLE展开为多行：

WITH joined_data AS (
    SELECT 
        o.org_id,
        o.org_name,
        c.city_name,
        r.relation_type,
        o.service
    FROM Organization o
    JOIN Relation r ON o.org_id = r.org_id
    JOIN City c ON r.city_id = c.city_id
)
SELECT 
    jd.org_id,
    jd.org_name,
    jd.city_name,
    jd.relation_type,
    TRIM(JSON_UNQUOTE(json_value)) AS service
FROM joined_data jd
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(jd.service, ',', '","'), '"]'),
    '$[*]' COLUMNS(json_value VARCHAR(255) PATH '$')
) AS jt
WHERE jd.service IS NOT NULL AND jd.service != '';

MySQL 5.x（无JSON_TABLE，用递归CTE） #

对于不支持JSON函数的老版本，用递归方式逐次拆分字符串：

WITH RECURSIVE joined_data AS (
    SELECT 
        o.org_id,
        o.org_name,
        c.city_name,
        r.relation_type,
        o.service,
        1 AS pos,
        SUBSTRING_INDEX(o.service, ',', 1) AS single_service,
        SUBSTRING(o.service, LENGTH(SUBSTRING_INDEX(o.service, ',', 1)) + 2) AS remaining_service
    FROM Organization o
    JOIN Relation r ON o.org_id = r.org_id
    JOIN City c ON r.city_id = c.city_id
    WHERE o.service IS NOT NULL AND o.service != ''
    UNION ALL
    SELECT 
        org_id,
        org_name,
        city_name,
        relation_type,
        service,
        pos + 1,
        SUBSTRING_INDEX(remaining_service, ',', 1),
        SUBSTRING(remaining_service, LENGTH(SUBSTRING_INDEX(remaining_service, ',', 1)) + 2)
    FROM joined_data
    WHERE remaining_service IS NOT NULL AND remaining_service != ''
)
SELECT 
    org_id,
    org_name,
    city_name,
    relation_type,
    TRIM(single_service) AS service
FROM joined_data
ORDER BY org_id, pos;

PostgreSQL #

用string_to_array将字符串转为数组，再通过UNNEST展开：

SELECT 
    o.org_id,
    o.org_name,
    c.city_name,
    r.relation_type,
    TRIM(s.service) AS service
FROM Organization o
JOIN Relation r ON o.org_id = r.org_id
JOIN City c ON r.city_id = c.city_id
JOIN UNNEST(string_to_array(o.service, ',')) AS s(service) ON TRUE
WHERE o.service IS NOT NULL AND o.service != '';

SQL Server 2016+ #

使用官方内置的STRING_SPLIT函数，配合CROSS APPLY展开：

SELECT 
    o.org_id,
    o.org_name,
    c.city_name,
    r.relation_type,
    TRIM(s.value) AS service
FROM Organization o
JOIN Relation r ON o.org_id = r.org_id
JOIN City c ON r.city_id = c.city_id
CROSS APPLY STRING_SPLIT(o.service, ',') s
WHERE o.service IS NOT NULL AND o.service != '';

关键注意事项 #

• 过滤空值：通过WHERE条件排除service为空或空字符串的行，避免生成无效空行
• 清理空格：用TRIM处理拆分后的值，避免原字符串中逗号前后的空格残留
• 老版本兼容：如果使用极老版本数据库（如MySQL 5.x之前），可能需要自定义拆分函数替代递归CTE

内容的提问来源于stack exchange，提问作者Sprössling