Use casewhen_MySQL for row-to-column conversion

CREATETABLEbill (idCHAR (36) NOTNULL, customerINT (255) NULLDEFAULTNULLCOMMENT 'customer', shopINT (255) NULLDEFAULTNULLCOMMENT 'consumption shop'

Create table bill (id CHAR (36) not null, customer INT (255) null default null comment 'customer', shop INT (255) null default null comment 'consumption store ', money DECIMAL (255) null default null comment 'login', type INT () null default null comment' type 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-Hangzhou', 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-records 2-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 (255) null default null comment 'parameter 1', type INT) 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 ('d7b10133-d189-440b-9d7a-72465078c066 ', 'rumm', 9,200.00, 2 );

Hope to get something like this.
Type1 indicates the sum of all the money values of type = '1' in the bill table. type2 indicates the sum of all the money values of type = '2' in The bill table, the sum of all v1 data in the card_model table is a 'card 'class.
First analysis:
1. group by store to obtain their and
Figure 2 (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
The data does not contain type2.
Using IFNULL prevents null results. Using the alias "money" and "type_test" to get the same columns will not report an error.
2. how to convert type1 and card into a column named the first image?
Use case when

Select shop 'store', 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) 'sume' 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

Here we use case when and the alias type_test to judge and then obtain the desired result from the alias, and finally Group it by shop.