将逗号分隔的列值拆分(分解)为行 [英] Split (explode) comma delimited column values to rows

问题描述

说明

以下是表结构:

eligibility_table

ID COURSE_ID BRANCH_IDS1 501 621,622,6231 5021 503 6252 501 6212 505 6503 500

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

course_table

ID COURSE_ID1 5011 5021 5032 5012 5053 500

branch_table

ID BRANCH_ID1 6211 6221 6231 6252 6212 650

问题:

我正在努力为 branch_table 编写 SQL QUERY.我想写一个像

这样的查询

INSERT INTO branch_table SELECT --- from quality_table --

解决方案

UPDATED 你可以像这样用 SQL 做到这一点

INSERT INTO branch_table (id, branch_id)SELECT e.id, SUBSTRING_INDEX(SUBSTRING_INDEX(e.branch_ids, ',', n.n), ',', -1) branch_idFROM 资格_表 e CROSS JOIN(选择 a.N + b.N * 10 + 1 n从(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),(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按 n 排序) nWHERE 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 行和资格表中的所有行)
• WHERE 子句中的条件从结果集中过滤掉所有不需要的行

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

分支表中的结果:

<前>|身份证 |BRANCH_ID |------------------|1 |第621话|1 |第622话|1 |第623话|1 |第625话|2 |第621话|2 |650 |

这是SQLFiddle演示

Description

Following is Table Structure:

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

Now, I am making new table structure as describe below and inserting course_table,branch_table through eligibility_table. So following, final output I want

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

Problem:

I am struggling to write SQL QUERY for branch_table. I want to write a query like

INSERT INTO branch_table SELECT --- from eligibility_table --

解决方案

UPDATED You can do it with SQL like this

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

• The subquery with an alias of n generates on the fly a sequence of numbers (numbers or tally table) from 1 to 100 in this particular case using UNION ALL and CROSS JOIN. Sometimes it's handy to have a real tally table in your db.
• In outer select innermost SUBSTRING_INDEX() gets everything up to n'th element in a list and outer SUBSTRING_INDEX() extract right most part after a last delimiter effectively getting n-th element itself.
• CROSS JOIN allows us to produce a set of rows which is a cartesian product (of 100 rows in n and all rows in eligibility_table)
• condition in WHERE clause filters out all unnecessary rows from the resultset

Note: this query will split up to 100 branch ids. If you need more or less you can adjust a limit by editing the inner subquery

Result in branch_table:

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

Here is SQLFiddle demo

这篇关于将逗号分隔的列值拆分(分解)为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！