sql - 我如何将日期分组（一月、二月、三月等）

Question

嗨，我有一张这样的桌子

+-------+------------+----------+---------+
| 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有点生疏。谢谢

标签： sqlsql-server

Answer 1

我会使用条件聚合：

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;