b******t
发帖数: 10 | 1 have a table
col1 col2
a1 b11,b12
a2 b21,b22
...
want to make a new table as
col1 col2
a1 b11
a1 b12
a2 b21
a2 b22
...
how to do this in MySQL?
thanks | s**********o
发帖数: 14359 | 2 倒出去，重新IMPORT进来，然后INSERT INTO TARGET TABLE
QUESTION WHAT IF DUPLICATE
col1 col2
a1 b11
a1 b11
a1 b11
a1 b12
a2 b21
a2 b22 | y****w
发帖数: 3747 | 3 发包子吧，这个板上人们都腻歪做作业了。
【在 b******t 的大作中提到】
: have a table
: col1 col2
: a1 b11,b12
: a2 b21,b22
: ...
: want to make a new table as
: col1 col2
: a1 b11
: a1 b12
: a2 b21
| b******t
发帖数: 10 | | b******t
发帖数: 10 | 5 好啊，包子什么价啊，我只有伪币0.8都给你，
【在 y****w 的大作中提到】
: 发包子吧，这个板上人们都腻歪做作业了。
| s**********o
发帖数: 14359 | | m*********2
发帖数: 178 | | y*****g
发帖数: 677 | 8 My solution is only work for fixed number of values in the col2,
for simplicity, if there is only 2 values in col2,
the following works:
insert into combined select a.col1, substring_index(a.col2,',',1) from
mytest a union all select b.col1,substring_index(b.col2,',',-1) from mytest
b;
mysql> select * from newt order by col1;
+------+-------------------------------+
| col1 | substring_index(a.col2,',',1) |
+------+-------------------------------+
| a1 | b11 |
| a1 | b12 |
| a2 | b21 |
| a2 | b22 |
+------+-------------------------------+
4 rows in set (0.04 sec) |
|
