将逗号分隔的列值拆分为行。

Question

描述

以下是表结构：

资格表

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。因此，我想要以下最终输出： course_table

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

branch_table

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

问题:

我在编写分支表的SQL查询语句方面遇到了困难。我希望编写一个像下面这样的查询语句：

INSERT INTO branch_table SELECT --- from eligibility_table --

- Anurag

---

你想从资格表中选择什么？是那些不在分行表中的BRANCH_IDS吗？ - Dinup Kandel

据我所知，MySQL 没有将字符串拆分为多行的函数（其他一些数据库可以实现），而这正是您在此需要的。 - Bohemian

MySQL将列字符串拆分为行 - John Woo

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在此特定情况下生成从1到100的数字序列（数字或记数表）。有时在您的数据库中拥有一个真正的计数表会很方便。
• 在外部选择内部最里面，SUBSTRING_INDEX()获取列表中的前n个元素的所有内容，而外部的SUBSTRING_INDEX()则提取最后一个分隔符后的右侧部分，有效地获得第n个元素本身。
• CROSS JOIN允许我们生成一组行，即笛卡尔积（n中的100行和eligibility_table中的所有行）
• WHERE子句中的条件过滤掉结果集中的所有不必要的行

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

分支表中的结果：

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

这里是SQLFiddle演示

- peterm

Answer 2

如果我理解正确，您正在更改数据库结构以使其实际上达到规范化，并且这是一次性的事情，我建议您在代码中完成此操作。连接到数据库，读取旧表并插入新表。您还可以添加一些错误检查和异常处理！只需选择您喜欢的语言，然后对逗号分隔值进行标准的split()操作，并将这些值插入表中。如果不起作用，则可以打印出哪行出错，以便您可以手动解决这些问题（这在sql中将更加困难）。

- Nanne

---

是的，如果没有其他方法，那么我会选择这种方式。 - Anurag