mysql数据透视表

如果有下图的需求：

需求

首先根据产品来统计每个月的销售金额，还有产品所对应的渠道每个月的金额。

首先创建sale表：

CREATE TABLE sale  (
    saledate date NULL DEFAULT NULL,
    product varchar(50) NULL DEFAULT NULL COMMENT '产品',
    channel varchar(50) NULL DEFAULT NULL COMMENT '渠道',
    amount decimal(14, 2) NULL DEFAULT NULL COMMENT '金额'
) ENGINE = InnoDB

插入测试数据：

测试数据

首先可以用case行转列算出每个月份的销售总额，extract函数可以从日期里提取出月份，sql如下：

SELECT product AS '产品',
    channel AS '渠道',
    sum(CASE EXTRACT(MONTH FROM saledate) WHEN 1 THEN amount ELSE 0 END) AS '1月',
    sum(CASE EXTRACT(MONTH FROM saledate) WHEN 2 THEN amount ELSE 0 END) AS '2月',
    sum(amount) AS '合计'
FROM sale GROUP BY product,channel;

查询结果如下：

查询结果

用mysql自带的rollup函数就可以实现上述的透视表功能，rollup可以在group by product,channel的基础上再进行group by product得到以产品为分组的汇总，然后再整体求一下sum得到总计。
查询语句如下：

SELECT IFNULL(product,'总计') AS '产品',
    IFNULL(channel,'--') AS '渠道',
    sum(CASE EXTRACT(MONTH FROM saledate) WHEN 1 THEN amount ELSE 0 END) AS '1月',
    sum(CASE EXTRACT(MONTH FROM saledate) WHEN 2 THEN amount ELSE 0 END) AS '2月',
    sum(amount) AS '合计'
FROM sale GROUP BY product,channel with ROLLUP;

我们一起来看一下执行结果，激动人心的一刻来了，擦亮我们的眼睛。

数据透视表