mysql - MySQL将多个值拆分为多行
问题描述
我需要帮助将多个值从多列拆分到另一列。下面是一个例子
CREATE TABLE split
(
`Col_1` VARCHAR(120),
`Col_2` VARCHAR(50),
`Col_3` VARCHAR(20),
`Col_4` VARCHAR(50)
);
Insert into split (Col_1,Col_2,Col_3,Col_4) values ('ABC','1','10',null);
Insert into split (Col_1,Col_2,Col_3,Col_4) values ('DEF','2,3','30,40',null);
Insert into split (Col_1,Col_2,Col_3,Col_4) values ('GHI','4,5','50','500,600,700');
select * from split;
+-------+-------+-------+-------------+
| Col_1 | Col_2 | Col_3 | Col_4 |
+-------+-------+-------+-------------+
| ABC | 1 | 10 | NULL |
| DEF | 2,3 | 30,40 | NULL |
| GHI | 4,5 | 50 | 500,600,700 |
+-------+-------+-------+-------------+
我不是这方面的专家,但一直在玩,并设法仅将 col_2 拆分为多行,如下所示:
SELECT
Col_1,Col_2,Col_3,Col_4,
SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_2, ',', numbers.n), ',', -1) Col_2_NEW,
SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_3, ',', numbers.n), ',', -1) Col_3_NEW,
SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_4, ',', numbers.n), ',', -1) Col_4_NEW
FROM
(SELECT 1 n UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN split
ON CHAR_LENGTH(split.Col_2) - CHAR_LENGTH(REPLACE(split.Col_2, ',', ''))>=numbers.n-1
ORDER BY Col_2, n;
+-------+-------+-------+-------------+-----------+-----------+-----------+
| Col_1 | Col_2 | Col_3 | Col_4 | Col_2_NEW | Col_3_NEW | Col_4_NEW |
+-------+-------+-------+-------------+-----------+-----------+-----------+
| ABC | 1 | 10 | NULL | 1 | 10 | NULL |
| DEF | 2,3 | 30,40 | NULL | 2 | 30 | NULL |
| DEF | 2,3 | 30,40 | NULL | 3 | 40 | NULL |
| GHI | 4,5 | 50 | 500,600,700 | 4 | 50 | 500 |
| GHI | 4,5 | 50 | 500,600,700 | 5 | 50 | 600 |
+-------+-------+-------+-------------+-----------+-----------+-----------+
但是,我也想将 col_3 和 col_4 拆分为新的,所以它给了我下面的输出。
+-------+-------+-------+-------------+-----------+-----------+-----------+
| Col_1 | Col_2 | Col_3 | Col_4 | Col_2_NEW | Col_3_NEW | Col_4_NEW |
+-------+-------+-------+-------------+-----------+-----------+-----------+
| ABC | 1 | 10 | NULL | 1 | 10 | NULL |
| DEF | 2,3 | 30,40 | NULL | 2 | 30 | NULL |
| DEF | 2,3 | 30,40 | NULL | 2 | 40 | NULL |
| DEF | 2,3 | 30,40 | NULL | 3 | 30 | NULL |
| DEF | 2,3 | 30,40 | NULL | 3 | 40 | NULL |
| GHI | 4,5 | 50 | 500,600,700 | 4 | 50 | 500 |
| GHI | 4,5 | 50 | 500,600,700 | 4 | 50 | 600 |
| GHI | 4,5 | 50 | 500,600,700 | 4 | 50 | 700 |
| GHI | 4,5 | 50 | 500,600,700 | 5 | 50 | 500 |
| GHI | 4,5 | 50 | 500,600,700 | 5 | 50 | 600 |
| GHI | 4,5 | 50 | 500,600,700 | 5 | 50 | 700 |
+-------+-------+-------+-------------+-----------+-----------+-----------+
到目前为止,我已经进行了全面搜索,他们仅将行拆分为一列,并且无法找到与我类似的问题。也许缺少一些连接或一些联合,我不知道,因为我不擅长查询。有人能帮我一下吗?不要求我阅读指南或手册 :-)
提前致谢
解决方案
您可以在此处尝试此线程中的建议之一。类似的东西
SELECT s.[Col_1], Split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT [Col_1],
CAST ('<M>' + REPLACE([Col_2], ',', '</M><M>') + '</M>' AS XML) AS String
FROM split) AS s
CROSS APPLY String.nodes ('/M') AS Split(a);
遍历您的列应该可以正常工作。
编辑:没看到这是 MySQL,抱歉。请参阅下面的工作解决方案。
以下代码应适用于前两列。
1.)创建表:
CREATE TABLE split(
`Col_1` VARCHAR(120),
`Col_2` VARCHAR(50),
`Col_3` VARCHAR(20),
`Col_4` VARCHAR(50)
);
INSERT INTO split (Col_1,Col_2,Col_3,Col_4) values ('ABC','1','10',null);
INSERT INTO split (Col_1,Col_2,Col_3,Col_4) values ('DEF','2,3','30,40',null);
INSERT INTO split (Col_1,Col_2,Col_3,Col_4) values ('GHI','4,5','50','500,600,700');
这导致
SELECT * FROM split;
Col_1 Col_2 Col_3 Col_4
ABC 1 10 (null)
DEF 2,3 30,40 (null)
GHI 4,5 50 500,600,700
2.) 拆分 Col_2 中的字符串:
SELECT
split.Col_1,
SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_2, ',', numbers.n), ',', -1) Col_2,
Col_3,
Col_4
FROM
(select 1 n UNION ALL
select 2 UNION ALL select 3 UNION ALL
select 4 UNION ALL select 5) numbers INNER JOIN split
ON CHAR_LENGTH(split.Col_2)
-CHAR_LENGTH(REPLACE(split.Col_2, ',', ''))>=numbers.n-1
ORDER BY Col_1, Col_2;
3.) 结果:
Col_1 Col_2 Col_3 Col_4
ABC 1 10 (null)
DEF 2 30,40 (null)
DEF 3 30,40 (null)
GHI 4 50 500,600,700
GHI 5 50 500,600,700
这是上面代码的 SQL 小提琴:http ://sqlfiddle.com/#!9/948fcb/4 。您应该能够从那里进行迭代。如果您需要更多指导,请评论此帖子。
重要警告:这适用于一列中最多 5 个逗号分隔的字符串。
解决方案的灵感来自 fthiella 对SQL split values to multiple rows的回答。
推荐阅读
- c# - 在新选项卡中打开 .pdf 文件
- asp.net-core-mvc - 访问令牌和刷新令牌的存储位置和方式
- java - 在没有设置 chunkedStreamingMode 的情况下,在上传过程中使用 HttpURLConnection 时如何测量上传速度?
- r - 从纵向数据集中的年龄生成后续时间变量?
- r - GAM with mrf smooth - 错误(nb/polys 区域名称和数据区域名称不匹配
- ios - Swift 4 - 'substring(to:)' 已弃用 - 我的代码的替代方案
- javascript - JavaScript 参考下降
- haskell - (a == b) 的类型可以派生为多态吗?
- html - 图片链接问题:行为差异(IE vs Chrome)
- sql-server - 从 T SQL 存储过程调用 MDX