2
社区成员
![](https://csdnimg.cn/release/cmsfe/public/img/topic.427195d5.png)
![](https://csdnimg.cn/release/cmsfe/public/img/me.40a70ab0.png)
![](https://csdnimg.cn/release/cmsfe/public/img/task.87b52881.png)
![](https://csdnimg.cn/release/cmsfe/public/img/share-circle.3e0b7822.png)
现有销售记录表
create table sales(
id serial primary key ,
sku_id integer not null ,
amount money,
meta jsonb default '{}'::jsonb,
created_at timestamp default now()
);
create index on sales(created_at);
现在我们希望对这个表做一个月度的透视汇总,得到2020年每个月每种商品(sku_id)的的销售总额,每个月一列,哪一项可以实现?
select sku_id,
sum(case extract(month from created_at) when 1 then amount else 0::money end) as Jan,
sum(case extract(month from created_at) when 2 then amount else 0::money end) as Feb,
sum(case extract(month from created_at) when 3 then amount else 0::money end) as Mar,
sum(case extract(month from created_at) when 4 then amount else 0::money end) as Apr,
sum(case extract(month from created_at) when 5 then amount else 0::money end) as May,
sum(case extract(month from created_at) when 6 then amount else 0::money end) as June,
sum(case extract(month from created_at) when 7 then amount else 0::money end) as July,
sum(case extract(month from created_at) when 8 then amount else 0::money end) as Aug,
sum(case extract(month from created_at) when 9 then amount else 0::money end) as Sept,
sum(case extract(month from created_at) when 10 then amount else 0::money end) as Oct,
sum(case extract(month from created_at) when 11 then amount else 0::money end) as Nov,
sum(case extract(month from created_at) when 12 then amount else 0::money end) as Dec
from sales
where created_at between '2020-01-01'::timestamp and '2021-01-01'::timestamp
group by sku_id;