mysql - 将逗号分隔的列值拆分(分解)为行

Question

描述

表结构如下:

eligibility_table

ID     COURSE_ID     BRANCH_IDS
1      501           621,622,623
1      502
1      503           625
2      501           621
2      505           650
3      500

现在，我正在制作如下所述的新表结构，并通过 eligibility_table 插入 course_table、branch_table。所以接下来，我想要的最终输出

类(class)表

ID COURSE_ID
1  501
1  502
1  503
2  501
2  505
3  500

分支表

ID BRANCH_ID
1  621
1  622
1  623
1  625
2  621
2  650

问题:

我正在努力为 branch_table 编写 SQL 查询。我想写一个这样的查询

INSERT INTO branch_table SELECT --- from eligibility_table --

Answer 1

已更新您可以像这样使用 SQL 来完成

INSERT INTO branch_table (id, branch_id)
SELECT e.id, SUBSTRING_INDEX(SUBSTRING_INDEX(e.branch_ids, ',', n.n), ',', -1) branch_id
  FROM eligibility_table e CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(e.branch_ids) - LENGTH(REPLACE(e.branch_ids, ',', '')))
 ORDER BY id, branch_id

• 在这种特殊情况下，别名为 n 的子查询使用 UNION ALL 和 CROSS JOIN 。 有时在您的数据库中有一个真正的计数表会很方便。
• 在最外面选择最里面SUBSTRING_INDEX()获取列表中直到第 n 个元素的所有内容，并且外部 SUBSTRING_INDEX() 在最后一个定界符之后提取最右边的部分，有效地获取第 n 个元素本身。
• CROSS JOIN 允许我们生成一组行，它是笛卡尔积(n 中的 100 行和 eligibility_table 中的所有行)
• WHERE 子句中的条件从结果集中过滤掉所有不需要的行

注意:此查询将拆分多达 100 个分支 ID。如果您需要更多或更少，您可以通过编辑内部子查询来调整限制

branch_table 中的结果:

| ID | BRANCH_ID |------------------|  1 |       621 ||  1 |       622 ||  1 |       623 ||  1 |       625 ||  2 |       621 ||  2 |       650 |

这是 SQLFiddle 演示