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

来源：互联网发布：乐视高清直播软件编辑：程序博客网时间：2024/05/16 05:20

资料来源：http://blog.chinaunix.net/uid-411974-id-3990697.html

用过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;

0 0