数据透视表

最新推荐文章于 2025-08-13 21:05:35 发布

原创最新推荐文章于 2025-08-13 21:05:35 发布 · 188 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#前端 #数据库 #javascript

假设我有一个表，里面有产品，渠道，销售日期，销量字段

现在想要达到想excel里面数据透视表的功能

比如如下效果

数据提供

INSERT INTO sales_data VALUES ('2019-01-01','桔子','淘宝',1864.00);
INSERT INTO sales_data VALUES ('2019-01-01','桔子','京东',1329.00);
INSERT INTO sales_data VALUES ('2019-01-01','桔子','店面',1736.00);
INSERT INTO sales_data VALUES ('2019-01-01','香蕉','淘宝',1573.00);
INSERT INTO sales_data VALUES ('2019-01-01','香蕉','京东',1364.00);
INSERT INTO sales_data VALUES ('2019-01-01','香蕉','店面',1178.00);
INSERT INTO sales_data VALUES ('2019-01-01','苹果','淘宝',511.00);
INSERT INTO sales_data VALUES ('2019-01-01','苹果','京东',568.00);
INSERT INTO sales_data VALUES ('2019-01-01','苹果','店面',847.00);
INSERT INTO sales_data VALUES ('2019-01-02','桔子','淘宝',1923.00);
INSERT INTO sales_data VALUES ('2019-01-02','桔子','京东',775.00);
INSERT INTO sales_data VALUES ('2019-01-02','桔子','店面',599.00);
INSERT INTO sales_data VALUES ('2019-01-02','香蕉','淘宝',1612.00);
INSERT INTO sales_data VALUES ('2019-01-02','香蕉','京东',1057.00);
INSERT INTO sales_data VALUES ('2019-01-02','香蕉','店面',1580.00);
INSERT INTO sales_data VALUES ('2019-01-02','苹果','淘宝',1345.00);
INSERT INTO sales_data VALUES ('2019-01-02','苹果','京东',564.00);
INSERT INTO sales_data VALUES ('2019-01-02','苹果','店面',1953.00);
INSERT INTO sales_data VALUES ('2019-02-01','桔子','淘宝',1324.00);
INSERT INTO sales_data VALUES ('2019-02-01','桔子','京东',817.00);
INSERT INTO sales_data VALUES ('2019-02-01','桔子','店面',835.00);
INSERT INTO sales_data VALUES ('2019-02-01','香蕉','淘宝',1233.00);
INSERT INTO sales_data VALUES ('2019-02-01','香蕉','京东',721.00);
INSERT INTO sales_data VALUES ('2019-02-01','香蕉','店面',1221.00);
INSERT INTO sales_data VALUES ('2019-02-01','苹果','淘宝',2145.00);
INSERT INTO sales_data VALUES ('2019-02-01','苹果','京东',1152.00);
INSERT INTO sales_data VALUES ('2019-02-01','苹果','店面',900.00);
INSERT INTO sales_data VALUES ('2019-02-02','桔子','淘宝',1665.00);
INSERT INTO sales_data VALUES ('2019-02-02','桔子','京东',1606.00);
INSERT INTO sales_data VALUES ('2019-02-02','桔子','店面',1070.00);
INSERT INTO sales_data VALUES ('2019-02-02','香蕉','淘宝',1247.00);
INSERT INTO sales_data VALUES ('2019-02-02','香蕉','京东',2158.00);
INSERT INTO sales_data VALUES ('2019-02-02','香蕉','店面',709.00);
INSERT INTO sales_data VALUES ('2019-02-02','苹果','淘宝',1406.00);
INSERT INTO sales_data VALUES ('2019-02-02','苹果','京东',1437.00);
INSERT INTO sales_data VALUES ('2019-02-02','苹果','店面',952.00);

首先我们先要给销售日期行专列，这就需要根据产品和渠道进行分组

然后进行行转列 sum( case when month(sales_date) = '1' then amount end) as 1月

其他月份也如此

这样就得到了，每个产品每个渠道，每个月份的销量，但是我们还缺少一个总量

这就需要用到 rollup了

具体代码如下

select coalesce(product,'总量') as 产品
    ,coalesce(channel,'---') as 渠道
    ,sum(case when month(saledate) = 1 then amount end ) as 1月
    ,sum(case when month(saledate) = 2 then amount end ) as 2月
    ,sum(case when month(saledate) = 3 then amount end ) as 3月
    ,sum(case when month(saledate) = 4 then amount end ) as 4月
    ,sum(case when month(saledate) = 5 then amount end ) as 5月
    ,sum(case when month(saledate) = 6 then amount end ) as 6月
from sales_data
group by product,channel with rollup