mysql - 如何从一列中分离多个数据？

Question

我遇到了一个问题 - 我需要将数据与 Column3 列(分隔符“|”)分开。

Column1 Column2 Column3
7        23      567|568|85|532|296|581|300|265|577|330|563|423|55|442
8         0      242
9         0      242
10       23      567|568|85|532|296|581|300|265|577|330|563|423|55|442
14        4      330|563|423|134|242

Column1 是 ID，Column2 是 '|'对每一行进行计数，第 3 列是应该在新行中分开的数据。

我的输出应该是这样的:

Column1 Column4
7       567
8       242
9       242
10      567
14      330
7       568
10      568
14      563

我像下面这样写了 union，但我不想重复它 60 次...

select 
    Column1,
    substring_index(substring_index(Column2,'|',1),'|',-1) as Column2
from Table1
union
select
    Column1,
    substring_index(substring_index(Column2,'|',2),'|',-1) as Column2
from Table1

你能帮我找到更好的解决方案吗？

BR

Answer 1

你可以用一个数字列表来做到这一点。这是一个例子:

select Column1,
       substring_index(substring_index(Column2,'|', nums.n),'|',-1) as Column2
from Table1 t1 cross join
      (select (n1.n - 1) * 12 + (n2.n - 1)*3 + n3.n as n
       from  (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
             ) n1 cross join
             (select 1 as n union all select 2 union all select 3 union all select 4 
             ) n2 cross join
             (select 1 as n union all select 2 union all select 3 
             ) n3
      ) nums