php - 如何将逗号分隔的字符串转换为单列？

Question

表:

id    brand    group
1     adidas    1,2
2     puma      1
3     asianone  1,2,3

我想要什么结果:

id    brand    group
1     adidias  1
2     adidias  2
3     puma     1
4     asianone 1
5     asianone 2
6     asianone 3

其中 id 是自增的..

Answer 1

SELECT @id:=@id+1 AS id,
       `brand`,
       `group` 
FROM
(SELECT
  `id`, 
  `brand`,
   SUBSTRING_INDEX(SUBSTRING_INDEX(`group`, ',', n.digit+1), ',', -1) AS `group`
FROM
  Table1
  INNER JOIN
  (SELECT 0 AS digit 
   UNION ALL SELECT 1 
   UNION ALL SELECT 2 
   UNION ALL SELECT 3  
   UNION ALL SELECT 4 
   UNION ALL SELECT 5 
   UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(`group`, ',' , '')) <= LENGTH(`group`)-n.digit
  Order by ID,`group`) AS T,(SELECT @id:=0) AS R;

输出

id  brand   group
1   adidas      1
2   adidas      2
3   puma        1
4   asianone    1
5   asianone    2
6   asianone    3

演示

http://sqlfiddle.com/#!9/be29c2/31

有关解释，请转到此链接并查看我的回答

MySql : Convert Column data to row