PostgreSQL按月份和类型分组数据

使用条件聚合:

select date_trunc('month', date) as mon,
       sum((item_type = apple)::int) as apple,
       sum((item_type = banana)::int) as banana
from t
group by mon
order by mon;

无关提示,但我也不会使用“日期”作为列名,因为它是pgsql中的保留字。

顺便说一句,如果我没弄错的话,查询应该是这样的:

select date_trunc('month', date) as mon,
       sum((item_type = 'apple')::int) as apple,
       sum((item_type = 'banana')::int) as banana
from t
group by mon
order by mon;

为了避免Postgres发出错误,因为我假设“item_type”存储为字符串变量。