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(',',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 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
