Mysql group_concat的反向应用实现（Mysql列转行）

分类： Mysql/postgreSQL

用过Mysql的都知道她有一个很好的实现行转列功能的函数group_concat函数，非常方便

点击(此处)折叠或打开

1. SELECT
   

2.     *
   

3. FROM
   

4.     group_test;
   

5.  
   

6. SELECT
   

7.     id,
   

8.     GROUP_CONCAT(sub_id)
   

9. FROM
   

10.     `group_test`
    

11. GROUP BY
    

12.     id;

现在的需求是有上面图二类似的结果集，需要把列二拆分 转换成行记录


我们知道如果是单条记录通过SUBSTRING_INDEX容易实现

点击(此处)折叠或打开

1. select id,SUBSTRING_INDEX(sub_id,',',1) from group_test where id=3
   

2. UNION
   

3. select id,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_id,',',2),',',-1) from group_test where id=3
   

4. UNION
   

5. select id,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_id,',',3),',',-1) from group_test where id=3

但是如果是N条呢？同样也是可以使用SUBSTRING_INDEX来实现，只不过需要一个配置表，通过CROSS JOIN交叉连接实现，先看下CROSS JOIN

点击(此处)折叠或打开

1. SELECT
   

2.     *
   

3. FROM
   

4.     (SELECT 1 UNION SELECT 2) t1
   

5. CROSS JOIN (SELECT 3 UNION SELECT 4) t2

下面就通过CROSS JOIN和SUBSTRING_INDEX实现我们的需求，首先构建一个配置表

点击(此处)折叠或打开

1. CREATE TABLE digits (digit INT(1));
   

2. INSERT INTO digits
   

3. VALUES
   

4.     (0),
   

5.     (1),
   

6.     (2),
   

7.     (3),
   

8.     (4),
   

9.     (5),
   

10.     (6),
    

11.     (7),
    

12.     (8),
    

13.     (9);
    

14. CREATE TABLE sequence (seq INT(3));
    

15. INSERT INTO sequence (
    

16.     SELECT
    

17.         D1.digit + D2.digit * 10
    

18.     FROM
    

19.         digits D1
    

20.     CROSS JOIN digits D2
    

21. );

然后

点击(此处)折叠或打开

1. SELECT
   

2.     id,
   

3.     SUBSTRING_INDEX(
   

4.         SUBSTRING_INDEX(sub_id, ',', seq),
   

5.         ',' ,- 1
   

6.     ) sub_id,
   

7.     seq
   

8. FROM
   

9.     sequence
   

10. CROSS JOIN group_test
    

11. WHERE
    

12.     seq BETWEEN 1
    

13. AND (
    

14.     SELECT
    

15.         1 + LENGTH(sub_id) - LENGTH(REPLACE(sub_id, ',', ''))
    

16. )
    

17. ORDER BY
    

18.     id,
    

19.     sub_id;

然后就没有然后了。如图上的代码 有几个地方使用还是很巧妙的 不是吗？
最后此方法是不是比写个存储过程或者PHP/PYTHON简单些呢^_^

转载于:https://my.oschina.net/u/2611009/blog/666471