mysql - 根据列分隔记录

Question

我有四列。两列以逗号分隔。我正在尝试获取这些逗号分隔值的单独记录。

col  col2   col3   col4    
------------------------
1     1,2    2,3    4  
2     3,4    5      7  
4     5      3      5 

我的结果集将是

col1   col2   col3   col4  
--------------------------
1        1      2     4  
1        1      3     4  
1        2      2     4  
1        2      3     4  
2        3      5     7  
2        4      5     7  
4        5      3     5

我试过很多。但无法获得确切的数据集。提前致谢

Answer 1

这是一种使用两个派生的数字表从列表中获取第 nth 元素的方法:

select col1,
       substring_index(substring_index(col2, ',', n2.n), ',', -1) as col2,
       substring_index(substring_index(col3, ',', n3.n), ',', -1) as col3,
       col4
from t join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
     ) n2
     on t.col2 like concat(repeat('%,', n2.n - 1), '%') join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
     ) n3
     on t.col3 like concat(repeat('%,', n3.n - 1), '%')