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

Question

我想我在这里遇到了困难......:(

我正在尝试按月获取订单数，即使为零。这是问题查询:

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) 和 ISNULL(COUNT(OrderNumber), 0) 但我很确定分组导致了这种情况工作。

Answer 1

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

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;