将mySQL中的多个单元格拆分为多行

将mySQL中的多个单元格拆分为多行

This question already has answers here:
Is storing a delimited list in a database column really that bad?
                                
                                    (10个回答)
                                
                        
                                4个月前关闭。
            
                    
我有下表。

表格1

ID   icc      bcci
1    402,403  101,102
2    403,404  103,105,106


我想获取结果如下

结果

ID    icc    bcci
1     402    101
1     402    102
1     403    101
1     403    102
2     403    103
2     403    105
2     403    106
2     404    103
2     404    105
2     404    106

最佳答案

I have a table of integers - ints(i) running from 0-9...

DROP TABLE IF EXISTS my_bad_table;

CREATE TABLE my_bad_table
(id SERIAL PRIMARY KEY
,icc VARCHAR(255) NOT NULL
,bcci VARCHAR(255) NOT NULL
);

INSERT INTO my_bad_table VALUES
(1,'402,403','101,102'),
(2,'403,404','103,105,106');

[CREATE TABLE my_good_table AS]
SELECT DISTINCT a.id
     , a.icc
     , b.bcci
  FROM
(
SELECT DISTINCT id
              , SUBSTRING_INDEX(SUBSTRING_INDEX(icc,',',i+1),',',-1)icc
              , SUBSTRING_INDEX(SUBSTRING_INDEX(bcci,',',i+1),',',-1)bcci
           FROM my_bad_table
              , ints
) a
JOIN
(
SELECT DISTINCT id
              , SUBSTRING_INDEX(SUBSTRING_INDEX(icc,',',i+1),',',-1)icc
              , SUBSTRING_INDEX(SUBSTRING_INDEX(bcci,',',i+1),',',-1)bcci
           FROM my_bad_table
              , ints
) b
ON b.id = a.id
ORDER
BY id,icc,bcci;

+----+-----+------+
| id | icc | bcci |
+----+-----+------+
|  1 | 402 | 101  |
|  1 | 402 | 102  |
|  1 | 403 | 101  |
|  1 | 403 | 102  |
|  2 | 403 | 103  |
|  2 | 403 | 105  |
|  2 | 403 | 106  |
|  2 | 404 | 103  |
|  2 | 404 | 105  |
|  2 | 404 | 106  |
+----+-----+------+


然后在id,icc,bcci上添加一个PRIMARY KEY。

实际上,对于您的情况,更简单的查询就足够了,但是希望您能理解。

关于mysql - 将mySQL中的多个单元格拆分为多行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58165988/

10-10 17:08