如何用SQL处理COMMENTS表 将同一ID的拼接评论拆分为独立行

前置说明 #

你的示例中拼接后的原因无明显分隔符，属于不规范的存储逻辑，以下方案默认你实际存储时使用了不会出现在原因文本中的特殊分隔符（如|、^等），可直接替换方案中的分隔符即可适配。如果你确实没有加分隔符，可以参考文末特殊场景的处理方案。 #

不同数据库实现代码 #

MySQL 8.0+ #

WITH RECURSIVE split_result AS (
    SELECT
        IDS,
        COMMENTS AS all_comments,
        1 AS index_num,
        -- 截取第一个原因
        SUBSTRING_INDEX(COMMENTS, '|', 1) AS single_comment,
        -- 剩余未拆分的内容
        SUBSTRING(COMMENTS, LENGTH(SUBSTRING_INDEX(COMMENTS, '|', 1)) + 2) AS left_comments
    FROM 你的表名
    WHERE COMMENTS IS NOT NULL AND COMMENTS <> ''
    UNION ALL
    SELECT
        IDS,
        all_comments,
        index_num + 1,
        SUBSTRING_INDEX(left_comments, '|', 1),
        SUBSTRING(left_comments, LENGTH(SUBSTRING_INDEX(left_comments, '|', 1)) + 2)
    FROM split_result
    WHERE left_comments IS NOT NULL AND left_comments <> ''
)
SELECT IDS, single_comment AS COMMENTS
FROM split_result
ORDER BY IDS, index_num;

PostgreSQL #

直接使用内置拆分函数即可，代码最简单：

SELECT
    IDS,
    unnest(string_to_array(COMMENTS, '|')) AS COMMENTS
FROM 你的表名;

SQL Server 2016及以上 #

使用内置STRING_SPLIT函数实现：

SELECT
    t.IDS,
    s.value AS COMMENTS
FROM 你的表名 t
CROSS APPLY STRING_SPLIT(t.COMMENTS, '|') s

Oracle #

SELECT
    IDS,
    REGEXP_SUBSTR(COMMENTS, '[^|]+', 1, LEVEL) AS COMMENTS
FROM 你的表名
CONNECT BY REGEXP_SUBSTR(COMMENTS, '[^|]+', 1, LEVEL) IS NOT NULL
AND PRIOR IDS = IDS
AND PRIOR SYS_GUID() IS NOT NULL;

无分隔符特殊场景处理 #

如果你的存储逻辑确实是直接拼接原因没有加分隔符，建议先枚举所有可选退订原因存入配置表reason_config(reason_text)，再通过模糊关联实现拆分：

-- 以PostgreSQL为例，其他数据库语法仅需修改拼接符即可
SELECT
    t.IDS,
    r.reason_text AS COMMENTS
FROM 你的表名 t
INNER JOIN reason_config r 
    ON STRPOS(t.COMMENTS, r.reason_text) > 0

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