设为首页收藏本站
查看: 51|回复: 0

[PHP] mysql行转列利用casewhen_MySQL

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    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);  希望得到类似这种的。
    其中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

      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分组。
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表