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-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);

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