mysql导入竖杠分割的数据_MYSQL :逗号分隔串表，分解成竖表

MYSQL :逗号分隔串表，分解成竖表

[sql]

DROP TEMPORARY TABLE IF EXISTS Temp_Num ;

CREATE TEMPORARY TABLE Temp_Num ( xh INT PRIMARY KEY ); -- 创建数字辅助表

SET @i = 0;

INSERT INTO Temp_Num(xh) -- 写入数字辅助表

SELECT @i := @i+1

FROM AdDataCenter.`Ad_Targeting_Mobisage` a

LIMIT 0, 100 ;

SELECT b.AdGroupID , SUBSTRING( str_split , a.xh , LOCATE(',',CONCAT( str_split ,','), a.xh ) - a.xh ) AS splitstr

FROM Temp_Num a

CROSS JOIN

(SELECT AppCategory AS str_split ,app.*

FROM AdDataCenter.Ad_Targeting_Mobisage app

WHERE app.AdTargetingID IN (1,2,3,4) ) b

WHERE a.xh <= LENGTH( str_split )

AND SUBSTRING( CONCAT(',', str_split ), a.xh, 1) = ','

LIMIT 0 ,1000 ;

SELECT AppCategory AS str_split ,app.*

FROM AdDataCenter.Ad_Targeting_Mobisage app

WHERE app.AdTargetingID IN (1,2,3,4);

原数据

str_split

AdTargetingID

---------------------------------------------------------------------- ---------------

1,10,11,12,13,14,15,16,19,2,20,21,22,24,25,26,27,28,29,3,31,32,4,5,6,8 2

1,10,11,13,14,15,16,2,20,21,22,25,26,27,28,29,3,31,32,4,6,8

3

1,10,11,12,13,14,15,16,19,20,21,22,25,27,28,3,32,4,6,8 4

实现的效果是

AdGroupID splitstr

--------- ----------

2 1

2 10

2 11

2 12

2 13

2 14

2 15

2 16

2 19

2 2

2 20

2 21

2 22

2 24

2 25

2 26

2 27

2 28

2 29

2 3

2 31

2 32

2 4

2 5

2 6

2 8

3 1

3 10

3 11

3 13

3 14

3 15

3 16

3 2

3 20

3 21

3 22

3 25

3 26

3 27

3 28

3 29

3 3

3 31

3 32

3 4

3 6

3 8

4 1

4 10

4 11

4 12

4 13

4 14

4 15

4 16

4 19

4 20

4 21

4 22

4 25

4 27

4 28

4 3

4 32

4 4

4 6

4 8