mysql行转列利用casewhen_MySQL

CREATE TABLE bill (id CHAR(36) NOT NULL,customer INT(255) NULL DEFAULT NULL COMMENT ‘顾客’,shop INT(255) NULL DEFAULT NULL COMMENT ‘消费店铺’,money DECIMAL(10,2) NULL DEFAULT NULL COMMENT ‘花费’,type INT(255) NULL DEFAULT NULL COMMENT ‘类型 0’,PRIMARY KEY (id))COLLATE=’utf8_general_ci’ENGINE=InnoDB;INSERT INTO bill (id, customer, shop, money, type) VALUES (‘117f1a3c-ae68-42de-aa29-b9679a9a79f8’, 68, 9, 100.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘1606dd9a-5e1b-4bb6-9641-7508587aab56’, NULL, 9, 100.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘649d86ff-0271-4799-bc3c-173514f40f7c’, NULL, 9, 300.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘6d502fb6-9664-4f0f-8e2d-2fc9e21202b3’, 68, 9, 100.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘7036ba44-8143-4a5b-802f-522b39253572’, 68, 9, 100.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘7bcb427f-0eb1-4aa7-811c-997d7dffecb1’, 68, 9, 100.00, 3);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘8043bd41-54c9-43d1-bf4a-def04e744343’, 68, 16, 180.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘8fbbcc6c-fcb0-4e95-bfd6-19d2e895694f’, NULL, 9, 200.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘94fa7e96-ae4a-423e<strong>本文来源gao@daima#com搞(%代@#码@网2</strong>-9c18-069adf601822’, NULL, 9, 100.00, 1);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘a8388be4-3862-41ca-aa0a-867cb9c9966b’, 68, 9, 0.00, 6);INSERT INTO bill (id, customer, shop, money, type) VALUES (‘ec6713c6-4460-44f1-8f32-d4c409571855’, 68, 9, 100.00, 1);CREATE TABLE card_model (id CHAR(36) NOT NULL,name VARCHAR(255) NULL DEFAULT NULL,shop INT(255) NULL DEFAULT NULL,v1 DECIMAL(10,2) NULL DEFAULT NULL COMMENT ‘参数1’,type INT(255) NULL DEFAULT NULL,PRIMARY KEY (id))COLLATE=’utf8_general_ci’ENGINE=InnoDB;INSERT INTO card_model (id, name, shop, v1, type) VALUES (‘af7b7105-b3d0-4552-86a2-f187f4cbaabd’, ‘wedf’, 9, 100.00, 1);INSERT INTO card_model (id, name, shop, v1, type) VALUES (‘d7b10362-d189-440b-9d7a-72465078c066’, ‘frm’, 9, 200.00, 2);

希望得到类似这种的。
其中type1是bill表中type=’1’的所有的money的和，其中type2是bill表中type=’2’的所有的money的和，card_model表中所有v1的数据的和全部为’card’类
首先分析：
1、按店铺分组，要分别得到他们的和
第二张图(http://img.blog.gaodaima.com/20150828214433916)
select IFNULL(sum(b.money),0) money,shop,’type1’ type_test from bill b where b.type=1 group by b.shop
union
select IFNULL(sum(b.money),0) money,shop,’type2’ type_test from bill b where b.type=2 group by b.shop
union
select IFNULL(sum(b.v1),0) money,shop,’card’ type_test from card_model b group by b.shop
从数据看没有type2的数据。
使用IFNULL是防止产生null结果，利用别名money，type_test使其获得相同的列不会报错
2、怎样将type1和card还有一个type2转化为列即第一张图
利用case when

select shop ‘店铺’,sum((case type_test when ‘type1’ then money else 0 end)) ‘type1’,sum((case type_test when ‘type2’ then money else 0 end)) ‘type2’,sum((case type_test when ‘card’ then money else 0 end)) ‘card’,sum(money) ‘总和’from(select IFNULL(sum(b.money),0) money,shop,’type1’ type_test from bill b where b.type=1 group by b.shopunionselect IFNULL(sum(b.money),0) money,shop,’type2’ type_test from bill b where b.type=2 group by b.shopunionselect IFNULL(sum(b.v1),0) money,shop,’card’ type_test from card_model b group by b.shop)agroup by shop

这里利用case when 和别名type_test判断再取别名得到想要的结果，最后按shop分组。

喜欢 (0)赏

[搞代码]

分享 (0)