我怎样才能在几个月内分组日期（一月，二月，三月等）(How can i group dates in months (Jan, Feb, March, etc.))

你好，我有一个这样的桌子

+-------+------------+----------+---------+ | name | date | quantity | price | +-------+------------+----------+---------+ | art1 | 2017-01-05 | 10 | 5543.32 | +-------+------------+----------+---------+ | art2 | 2017-01-15 | 16 | 12.56 | +-------+------------+----------+---------+ | art3 | 2017-02-08 | 5 | 853.65 | +-------+------------+----------+---------+ | art4 | 2017-03-06 | 32 | 98.65 | +-------+------------+----------+---------+

我想展示这些信息

+-------+------------+----------+---------+ | name | January | February | March | +-------+------------+----------+---------+ | art1 | 5543.32 | 0 | 0 | +-------+------------+----------+---------+ | art2 | 12.56 | 0 | 0 | +-------+------------+----------+---------+ | art3 | 0 | 853.65 | 0 | +-------+------------+----------+---------+ | art4 | 0 | 0 | 98.65 | +-------+------------+----------+---------+

我怎样才能做到这一点？我在sql中有点生疏。谢谢

Hi i got 1 table like this

+-------+------------+----------+---------+ | name | date | quantity | price | +-------+------------+----------+---------+ | art1 | 2017-01-05 | 10 | 5543.32 | +-------+------------+----------+---------+ | art2 | 2017-01-15 | 16 | 12.56 | +-------+------------+----------+---------+ | art3 | 2017-02-08 | 5 | 853.65 | +-------+------------+----------+---------+ | art4 | 2017-03-06 | 32 | 98.65 | +-------+------------+----------+---------+

And i want to show this information

+-------+------------+----------+---------+ | name | January | February | March | +-------+------------+----------+---------+ | art1 | 5543.32 | 0 | 0 | +-------+------------+----------+---------+ | art2 | 12.56 | 0 | 0 | +-------+------------+----------+---------+ | art3 | 0 | 853.65 | 0 | +-------+------------+----------+---------+ | art4 | 0 | 0 | 98.65 | +-------+------------+----------+---------+

How can i do that? I'm a bit rusty in sql. Thanks

最满意答案

我会使用条件聚合：

select name, sum(case when date >= '2017-01-01' and date < '2017-02-01' then price else 0 end) as january, sum(case when date >= '2017-02-01' and date < '2017-03-01' then price else 0 end) as february, sum(case when date >= '2017-03-01' and date < '2017-04-01' then price else 0 end) as march from t group by name;

I would use conditional aggregation:

select name, sum(case when date >= '2017-01-01' and date < '2017-02-01' then price else 0 end) as january, sum(case when date >= '2017-02-01' and date < '2017-03-01' then price else 0 end) as february, sum(case when date >= '2017-03-01' and date < '2017-04-01' then price else 0 end) as march from t group by name;

更多推荐