如何拆分逗号分隔的subOperation字段编写SQL生成关联结果表

核心实现逻辑分为三步：

1. 拆分表中subOperation字段的逗号分隔值，单条记录拆分为多行，保留原记录id作为idSubOperation
2. 将拆分得到的每个subOperation值，关联原表中单值对应的id作为idElement
3. 对结果集生成自增序号作为最终输出的id字段

以下为不同数据库的实现代码，假设你的原表名为operation_tbl，可根据实际表名替换。

MySQL 8.0+ 实现 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY t.idSubOperation, ot2.id) AS id,
    t.idSubOperation,
    ot2.id AS idElement
FROM (
    -- 拆分逗号分隔的subOperation为多行
    SELECT 
        ot.id AS idSubOperation,
        jt.sub_op_val
    FROM operation_tbl ot
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(ot.subOperation, ',', '","'), '"]'),
        '$[*]' COLUMNS (sub_op_val VARCHAR(255) PATH '$')
    ) jt
) t
-- 关联原表获取对应idElement
JOIN operation_tbl ot2 ON ot2.subOperation = t.sub_op_val
ORDER BY id;

PostgreSQL 实现 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY t.idSubOperation, ot2.id) AS id,
    t.idSubOperation,
    ot2.id AS idElement
FROM (
    SELECT 
        ot.id AS idSubOperation,
        unnest(string_to_array(ot.subOperation, ',')) AS sub_op_val
    FROM operation_tbl ot
) t
JOIN operation_tbl ot2 ON ot2.subOperation = t.sub_op_val
ORDER BY id;

低版本MySQL（不支持JSON_TABLE）实现 #

使用递归CTE完成拆分：

WITH RECURSIVE split_cte AS (
    SELECT
        id AS idSubOperation,
        subOperation AS remaining_op,
        SUBSTRING_INDEX(subOperation, ',', 1) AS sub_op_val
    FROM operation_tbl
    UNION ALL
    SELECT
        idSubOperation,
        SUBSTRING(remaining_op, LENGTH(sub_op_val) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining_op, LENGTH(sub_op_val) + 2), ',', 1)
    FROM split_cte
    WHERE remaining_op LIKE '%,%'
)
SELECT
    ROW_NUMBER() OVER (ORDER BY sc.idSubOperation, ot.id) AS id,
    sc.idSubOperation,
    ot.id AS idElement
FROM split_cte sc
JOIN operation_tbl ot ON ot.subOperation = sc.sub_op_val
ORDER BY id;

• 请提前确认原表中包含所有拆分后subOperation值对应的单值记录，避免关联丢失数据
• 若subOperation值存在前后空格，可将关联条件替换为ot2.subOperation = TRIM(t.sub_op_val)避免匹配失败
• 若需要保留无匹配的拆分记录，可将代码中的JOIN替换为LEFT JOIN，无匹配时idElement返回NULL

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