我必须将MySQL表中的一列拆分为多行,每次出现的模式为":“和”“。以下是列数据的外观:
Column_1
page1:message1,page2:message2,page3:message3,page4:message4
我想展示这样的数据:-
Column_2 Column_1
page1 message1
page2 message2
page3 message3
page4 message4
我尝试过查找REGEXP_REPLACE()和SUBSTRING_INDEX() MySQL函数,但是无法形成一个查询来实现这一点。
谁来帮帮忙。
发布于 2022-09-10 13:13:23
如果您的MySQL版本不支持windows函数,您可以创建一个数字表(最大字符串长度),如下所示:
create table numbers (
nr int
);
insert into numbers values (1),(2),(3),(4),(5);
然后遵循两个步骤。
首先,用逗号分隔字符串,使用:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', nr),',', -1) AS new_col1
FROM test t
JOIN numbers n ON CHAR_LENGTH(col1) - CHAR_LENGTH(REPLACE(col1, ',', '')) >= nr - 1
你会得到这样的结果:
new_col1
page1:message1
page2:message2
page3:message3
page4:message4
其次,使用:
将行划分如下:
select SUBSTRING_INDEX(new_col1,':',1) as page,
SUBSTRING_INDEX(new_col1,':',-1) as message
from ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', nr),',', -1) AS new_col1
FROM test
JOIN numbers n ON CHAR_LENGTH(col1) - CHAR_LENGTH(REPLACE(col1, ',', '')) >= nr - 1
) as t2;
最终结果:
页面消息page1 message1 page2 message2 page3 message3 page4 message4
发布于 2022-09-10 12:11:26
您可以使用cte为每次出现的带有索引的行获取一行。
WITH RECURSIVE expand (column1, n) AS (
select column1, 1 from
(select
'page1:message1,page2:message2,page3:message3,page4:message4' as column1) as
table1
UNION ALL
select column1 , n+1 from expand where n < CHAR_LENGTH(column1) -
CHAR_LENGTH(REPLACE(column1,',',''))
)
这会给你一个结果
column1 n
page1:message1,page2:message2,page3:message3,page4:message4 1
page1:message1,page2:message2,page3:message3,page4:message4 2
page1:message1,page2:message2,page3:message3,page4:message4 3
这样,您现在就可以获取与之分隔的部分的第n次出现,并将该部分分割为两列。
Select
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',n),',',-1),':',1) as page,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',n),',',-1),':',-1)
as message from expand
然后你就会得到
page message
page1 message1
page2 message2
page3 message3
https://stackoverflow.com/questions/73671396
复制相似问题