mysql行轉列利用casewhen

mysql行轉列利用casewhen

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、按店鋪分組，要分別得到他們的和
第二張圖(http://img.blog.csdn.net/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.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分組。