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
