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 ‘參數(shù)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的數(shù)據(jù)的和全部為’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
從數(shù)據(jù)看沒有type2的數(shù)據(jù)。
使用IFNULL是防止產(chǎn)生null結(jié)果,利用別名money,type_test使其獲得相同的列不會報錯
2、怎樣將type1和card還有一個type2轉(zhuǎn)化為列即第一張圖
利用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判斷再取別名得到想要的結(jié)果,最后按shop分組。
聲明:本網(wǎng)頁內(nèi)容旨在傳播知識,若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系,我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com