专栏名称: 爱数据原统计网
中国统计网(www.itongji.cn),国内最大的数据分析门户网站。提供数据分析行业资讯,统计百科知识、数据分析、商业智能(BI)、数据挖掘技术,Excel、SPSS、SAS、R等数据分析软件等在线学习平台。
目录
相关文章推荐
51好读  ›  专栏  ›  爱数据原统计网

Mysql group_concat的反向应用实现(Mysql列转行)

爱数据原统计网  · 公众号  · BI  · 2017-02-23 17:22

正文


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

点击(此处)折叠或打开


SELECT

    *

FROM

    group_test;

 

SELECT

    id,

    GROUP_CONCAT(sub_id)

FROM

    `group_test`

GROUP BY

    id;



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


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

点击(此处)折叠或打开


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

UNION

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

UNION


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


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

点击(此处)折叠或打开


SELECT

    *

FROM

    (SELECT 1 UNION SELECT 2) t1

CROSS JOIN (SELECT 3 UNION SELECT 4) t2



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

点击(此处)折叠或打开


CREATE TABLE digits (digit INT(1));

INSERT INTO digits

VALUES

    (0),

    (1),

    (2),

    (3),

    (4),

    (5),

    (6),

    (7),

    (8),

    (9);

CREATE TABLE sequence (seq INT(3));

INSERT INTO sequence (

    SELECT

        D1.digit + D2.digit * 10

    FROM

        digits D1

    CROSS JOIN digits D2

);


然后

点击(此处)折叠或打开


SELECT

    id,

    SUBSTRING_INDEX(

        SUBSTRING_INDEX(sub_id, ',', seq),

        ',' ,- 1

    ) sub_id,

    seq

FROM

    sequence

CROSS JOIN group_test

WHERE

    seq BETWEEN 1

AND (

    SELECT

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

)

ORDER BY

    id,

    sub_id;



然后就没有然后了。如图上的代码 有几个地方使用还是很巧妙的 不是吗?


最后此方法是不是比写个存储过程或者PHP/PYTHON简单些呢^_^


End.


作者:云开_sky(中国统计网特邀认证作者)


本文为中国统计网原创文章,需要转载请联系中国统计网(小编微信:itongjilove),转载时请注明作者及出处,并保留本文链接。