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

页面导航:首页 > 数据库 > Mysql > MYSQL :逗号分隔串表,分解成竖表

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

来源: 作者: 时间:2016-02-18 10:28 【

MYSQL :逗号分隔串表,分解成竖表[sql] DROP TEMPORARY TABLE IF EXISTS Temp_Num ; CREATE TEMPORARY TABLE Temp_Num ( xh INT PRIMARY KEY ); -- 创建数字辅助表 ...
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        

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<