Home >Database >Mysql Tutorial >MYSQL :逗号分隔串表，分解成竖表_MySQL
MYSQL :逗号分隔串表，分解成竖表_MySQL
- WBOYOriginal
- 2016-06-01 13:31:151185browse
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
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：windows、linux 下启用mysql日志功能_MySQLNext article：浅析Mysql 数据回滚错误的解决方法_MySQL