sql-server - SQL 查询 : Count, 按日期和年份多列分组

Question

我有如下表格:

Date       | Type |
2016/04/01    A
2016/04/01    B
2016/04/02    B
2016/05/07    A 
   ...       ...

我尝试做一个查询来计算按月/年分组的每个类型的出现次数:

DateByYearMonth  |  A  |  B  |
04/2016             1     2
05/2016             1     0
...

我尝试了很多东西，但我得不到我想要的。我的“基本查询”是:

SELECT
  COUNT(1) AS A
FROM DBase.dbo.MyTable
WHERE [Type] = 'A'
AND Date BETWEEN '20140101' AND '20160930'
GROUP BY YEAR(Date),
         MONTH(Date)

谁能帮帮我？

Answer 1

所以你可以做类似的事情，在按年/月数据分组的数据上使用 PIVOT。

当然，您必须在 PIVOT 和主选择中为您的列添加 n 个所需数据

select 
   year, 
   month, 
   coalesce(A, 0) as A, 
   coalesce(B, 0) as B
from 
    (select  
       [Type], 
       Year([Date]) as year, 
       Month([Date]) as month, count(*) c
     from tt1
     group by [Type], Year([Date]), Month([Date])
     ) t

PIVOT(sum(c)
      for [Type] in ([A], [B])
) as pvt