在 Group By 查询中包含缺失的月份
我觉得我在这里遇到了困难... :(
I think I have a tough one here... :(
我正在尝试按月获取订单数，即使是零时也是如此.这是问题查询:
I am trying to get an order count by month, even when zero. Here's the problem query:
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)
我希望得到的是这样的:
What I'm looking to get is something like this:
Month Orders
----- ------
January 10
February 7
March 0
April 12
May 0
June 5
...但我的查询跳过了三月和五月的一行.我已经尝试过 COALESCE(COUNT(OrderNumber), 0) 和 ISNULL(COUNT(OrderNumber), 0) 但我很确定分组会导致不工作.
...but my query skips a row for March and May. I've tried COALESCE(COUNT(OrderNumber), 0) and ISNULL(COUNT(OrderNumber), 0) but I'm pretty sure the grouping is causing that not to work.
推荐答案
这个解决方案不需要你硬编码你可能想要的月份列表，你需要做的就是提供任何开始日期和任何结束日期，它将为您计算月份边界.它在输出中包含年份，以便支持 12 个月以上，并且您的开始和结束日期可以跨越年份边界，并且仍然正确排序并显示正确的月份和年.
This solution doesn't require you to hard-code the list of months you might want, all you need to do is provide any start date and any end date, and it will calculate the month boundaries for you. It includes year in the output so that it will support more than 12 months and so that your start and end dates can cross a year boundary and still order correctly and show the correct month 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;
相关文章