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

来源：互联网发布：淘宝关键词就是标题吗编辑：程序博客网时间：2024/06/06 05:46

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 aCROSS 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