CREATE TABLE test( paytype VARCHAR(10),goods VARCHAR(10),money INT) ENGINE=INNODB;
INSERT INTO test VALUES('微信','商品1',80);
INSERT INTO test VALUES('支付宝','商品2',120);
INSERT INTO test VALUES('支付宝','商品1',80);
INSERT INTO test VALUES('微信','商品1',60);
SELECT * FROM test;
要变成以下的数据显示:
SQL如下:
SELECT goods AS "商品",
SUM(money) AS "金额",
SUM(IF(paytype="微信",money,0)) AS "微信",
SUM(IF(paytype="支付宝",money,0)) AS "支付宝"
FROM test
GROUP BY goods