我怎样才能在几个月内分组日期(一月,二月,三月等)(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;更多推荐
发布评论