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