透视表

[community_1596]

现有销售记录表

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)的的销售总额，每个月一列，哪一项可以实现？

返回练习题答题页面

[CSDN-Ada助手]

@IT邦德 总结得很好，学到了！

[IT邦德]

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;