Mysql row-to-column conversion using case when, mysqlcase

Mysql row-to-column conversion using case when, mysqlcase

CREATE TABLEbill(
idCHAR (36) not null,
customerINT (255) null default null comment 'customer ',
shopINT (255) null default null comment 'consumption shop ',
moneyDECIMAL () null default null comment 'cost ',
typeINT (255) null default null comment 'Type 0 ',
Primary key (id)
)
COLLATE = 'utf8 _ general_ci'
ENGINE = InnoDB
;
INSERTbill(id,customer,shop,money,type) VALUES ('117f1a3c-ae68-42de-aa29-b9679a9a79f8 ', 68, 9,100.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('1606dd9a-5e1b-4bb6-9641-7508587aab56 ', NULL, 9,100.00, 1 );
INSERTbill(id,customer,shop,money,type() VALUES ('649d86ff-0271-4799-bc3c-173514f40f7c', NULL, 9,300.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('6d502fb6-9664-4f0f-8e2d-2fc9e21202b3 ', 68, 9,100.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('7036ba44-8143-4a5b-802f-522b39253572 ', 68, 9,100.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('7bcb427f-0eb1-4aa7-811c-997d7dffecb1 ', 68, 9,100.00, 3 );
INSERTbill(id,customer,shop,money,type) VALUES ('8043bd41-54c9-43d1-bf4a-def04e744343 ', 68, 16,180.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('8fbbcc6c-fcb0-4e95-bfd6-19d2e895694f ', NULL, 9,200.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('94fa7e96-ae4a-423e-9c18-069adf601822 ', NULL, 9,100.00, 1 );
INSERTbill(id,customer,shop,money,type) VALUES ('a8388be4-20172-41ca-aa0a-867cb9c9966b ', 68, 9, 0.00, 6 );
INSERTbill(id,customer,shop,money,type) VALUES ('ec6713c6-4460-44f1-8f32-d4c409571855', 68, 9,100.00, 1 );

CREATE TABLEcard_model(
idCHAR (36) not null,
nameVARCHAR (255) null default null,
shopINT (255) null default null,
v1DECIMAL (10, 2) null default null comment 'parameter 1 ',
typeINT (255) null default null,
Primary key (id)
)
COLLATE = 'utf8 _ general_ci'
ENGINE = InnoDB
;
INSERTcard_model(id,name,shop,v1,type) VALUES ('af7b7105-b3d0-4552-86a2-f187f4cbaabd ', 'wedf', 9,100.00, 1 );
INSERTcard_model(id,name,shop,v1,type) VALUES ('d7b10133-d189-440b-9d7a-72465078c066 ', 'frm', 9,200.00, 2 );

First figure (http://img.blog.csdn.net/20150828213643933)
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
The usage of case when in online search knows this is a choice statement, you can view (http://blog.csdn.net/yufaw/article/details/7600396) This blog Column
Figure 3 (http://img.blog.csdn.net/20150828215601788)
Select shop '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) 'Total'
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
)
Group 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.

Finally, you can use the IF statement as a row-to-column view (http://blog.csdn.net/zhoushengchao/article/details/7321688)

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.