mysql行转列利用case when[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

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-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、按店铺分组，要分别得到他们的和
第二张图
这里写图片描述
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
网上搜索下case when用法知道这个是一个选择语句，可以查看（http://blog.csdn.net/yufaw/article/details/7600396）这篇博客的列子
第三张图
这里写图片描述
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.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
)a
group by shop
这里利用case when 和别名type_test判断再取别名得到想要的结果，最后按shop分组。