mysql行轉列利用case when

標籤：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);

第一張圖(http://img.blog.csdn.net/20150828213643933)
希望得到類似這種的。
其中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
網上搜尋下case when用法知道這個是一個選擇語句，可以查看（http://blog.csdn.net/yufaw/article/details/7600396）這篇部落格的列子
第三張圖(http://img.blog.csdn.net/20150828215601788)
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分組。

最後還可以使用IF語句是行轉列，查看（http://blog.csdn.net/zhoushengchao/article/details/7321688）

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

mysql行轉列利用case when