描述
以下是表格结构:
合格性_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。下面,我想要的最终输出
课程表
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 QUERY。我想写一个类似的查询
INSERT INTO branch_table SELECT --- from eligibility_table --
更新您可以使用类似的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演示
如果我是正确的,你正在更改数据库结构,使其真正规范化,并且这是一次性的,我建议你在代码中这样做。连接到数据库,读取旧表并插入新表。您还可以添加一些错误检查和异常处理!
只需使用您喜欢的语言,然后对逗号分隔的值执行标准的split()
,并将这些值插入表中。如果它不起作用,你可以打印出哪一行出了问题,这样你就可以手动修复这些问题。(在sql中要实现这一点要困难得多)