更新时间:2022-12-10 08:27:03
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
UNION ALL
和 CROSS JOIN即时生成从 1 到 100 的数字序列(数字或计数表)代码>.有时在您的数据库中有一个真正的计数表会很方便.
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
UNION ALL
and CROSS JOIN
. Sometimes it's handy to have a real tally table in your db.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)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