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

bitsCN.com

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(&#39;,&#39;,CONCAT( str_split  ,&#39;,&#39;), 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(&#39;,&#39;, str_split  ), a.xh, 1) = &#39;,&#39;  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                21,10,11,13,14,15,16,2,20,21,22,25,26,27,28,29,3,31,32,4,6,8                                    31,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        

 

bitsCN.com