MySQL 行列相互转换 | 行列相互转换

行列相互转换

/*创建表*/

CREATE TABLE ic (

    NAME VARCHAR (20),

    Product VARCHAR (20),

    amount INT

);

INSERT INTO ic

VALUES

    ('王一', 'A1', 20),

    ('王一', 'A3', 150),

    ('王一', 'A2', 10),

    ('赵二', 'A1', 52),

    ('赵二', 'A2',46),

    ('赵二', 'A3',78),

    ('刘六', 'A1',55),

    ('刘六', 'A2',20),

    ('刘六', 'A3',86);

SELECT * FROM ic;

MySQL 行列相互转换-LMLPHP

想要用product列的值做列，列名变为 name，A1，A2，A3。

CREATE TABLE ic_1

SELECT name ,

SUM(IF(Product='A1',amount,0)) AS A1,

SUM(IF(Product='A2',amount,0)) AS A2,

SUM(IF(Product='A3',amount,0)) AS A3

FROM ic

GROUP BY name;

MySQL 行列相互转换-LMLPHP

如果将ic_1表再变回到原来的样子

SELECT name, 'A1' AS Product, A1 AS amount from ic_1

UNION

SELECT name, 'A2' AS Product, A2 AS amount from ic_1

UNION

SELECT name, 'A3' AS Product, A3 AS amount from ic_1

ORDER BY name;

MySQL 行列相互转换-LMLPHP