Mysql 列转行group_concat函数，与行转列

例一：

SELECT num from user

1、使用group_concat函数得到列转行

select group_concat(num) from user

2、使用SUBSTRING_INDEX和CROSS JOIN将列里面的的数字都拆分出来，把一行变成一列

方法一：建配置表（网上查询的方法）：

    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

    );

配置表sequence的结果为0-99的一列数字：

SQL：

SELECT

    SUBSTRING_INDEX(

        SUBSTRING_INDEX(num, ',', seq),

        ',' ,- 1

    ) num

FROM

    sequence

CROSS JOIN user

WHERE

    seq BETWEEN 1

AND (

    SELECT

        1 + LENGTH(num) - LENGTH(

            REPLACE (num, ',', '')

        )

)

方法二：将sequence替换为SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user LIMIT 0,100) ，其中user为表名，这张表需要大于100条。（自己不想建表，图省事）

SELECT  SUBSTRING_INDEX(

            SUBSTRING_INDEX(num, ',', seq),

            ',' ,- 1

        ) num

    FROM

        (SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user  LIMIT 0,100) b

    CROSS JOIN user

    WHERE

        seq BETWEEN 1

    AND (

        SELECT

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

两种方法结果均为：

例二：

将这样的数据拆分出来，变成如下：

还是使用上述sql，只需添加一列即可。(标记红色为新添加)

SELECT  ID,SUBSTRING_INDEX(

            SUBSTRING_INDEX(num, ',', seq),

            ',' ,- 1

        ) num

    FROM

        (SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user  LIMIT 0,100) b

    CROSS JOIN user

    WHERE

        seq BETWEEN 1

    AND (

        SELECT

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

4、补充

1) 本篇文章中，user为表名，num为字段，自行替换就好。

相关文章：Mysql语句优化

巴特西

Mysql 列转行group_concat函数，与行转列

1、使用group_concat函数得到列转行

2、使用SUBSTRING_INDEX和CROSS JOIN将列里面的的数字都拆分出来，把一行变成一列

最新文章

热门文章