在分组依据查询中包含缺失的月份

我想我这里有一个艰难的人......:(

我试图按月获取订单数，即使为零。这是问题查询：

SELECT datename(month, OrderDate) as Month, COUNT(OrderNumber) AS Orders
FROM OrderTable
WHERE OrderDate >= '2012-01-01' and OrderDate <= '2012-06-30'
GROUP BY year(OrderDate), month(OrderDate), datename(month, OrderDate)

我想要得到的是这样的：

Month            Orders
-----            ------
January          10
February         7
March            0
April            12
May              0
June             5

...但是我的查询跳过了三月和五月的一行。我试过了COALESCE(COUNT(OrderNumber), 0) and ISNULL(COUNT(OrderNumber), 0)但我很确定分组导致它不起作用。

此解决方案不需要您硬编码您可能想要的月份列表，您所需要做的就是提供任何开始日期和任何结束日期，它会为您计算月份边界。它在输出中包含年份，以便支持超过 12 个月，这样您的开始日期和结束日期就可以跨越年份边界，并且仍然正确排序并显示正确的月份and year.

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20120101', @EndDate = '20120630';

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT 
  [Month]    = DATENAME(MONTH, d.d), 
  [Year]     = YEAR(d.d), 
  OrderCount = COUNT(o.OrderNumber) 
FROM d LEFT OUTER JOIN dbo.OrderTable AS o
  ON o.OrderDate >= d.d
  AND o.OrderDate < DATEADD(MONTH, 1, d.d)
GROUP BY d.d
ORDER BY d.d;