SQL查询按年按月统计事件

Question

我有一张名为 Incidents 的表。我想要一个 SQL 查询，它返回按年、按月划分的事件计数。开始年份是 2010 年，但结束年份会有所不同。

示例事件表:

DateLogged    IncidentRef
-----------   ------------
2015-04-05    1
2014-06-04    2
2013-01-01    3
2012-12-10    4
2011-10-15    5
2010-10-01    6
2012-12-11    7
2011-10-10    8
2010-10-10    9

查询返回:

Year Jan Fev Mar Abr Mai Jun Jul Ago Set Out Nov Dez
2010  0   0   0   0   0   0   0   0   0   2   0   0
2011  0   0   0   0   0   0   0   0   0   2   0   0
2012  0   0   0   0   0   0   0   0   0   0   0   2
2013  1   0   0   0   0   0   0   0   0   0   0   0
2014  0   0   0   0   0   1   0   0   0   0   0   0
2015  0   0   0   1   0   0   0   0   0   0   0   0

如何进行该查询？我从哪里开始？

Answer 1

一种选择是使用条件聚合:

select year(datelogged), 
   sum(case when month(datelogged) = 1 then 1 else 0 end) Jan,
   sum(case when month(datelogged) = 2 then 1 else 0 end) Feb,
   ...,
   sum(case when month(datelogged) = 12 then 1 else 0 end) Dec
from yourtable
group by year(datelogged)

• SQL Fiddle Demo