我正在尝试计算一个月内产品的总销售额，但我希望它包括任何“空”月份（没有销售额）并且仅选择最近 12 个月。
这是我到目前为止的代码。
declare
@ProductNo int
set @ProductNo = 1234
SELECT
YEAR(o.OrderDate) as 'Year', MONTH(o.OrderDate) as 'Month', sum(Amount) as 'Units sold',[ProductNo]
FROM [OrderLine] ol
inner join [Order] o on ol.OrderNo = o.OrderNo
where ProductNo = @ProductNo
Group by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
Order by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
这返回
Year Month Units sold
2011 6 2
2011 10 1
2011 11 1
2012 2 1
但我希望它能回来。
Year Month Units sold
2011 4 0
2011 5 0
2011 6 2
2011 7 0
2011 8 0
2011 9 0
2011 10 1
2011 11 1
2011 12 0
2012 1 0
2012 2 2
2012 3 0
我使用的是 SQL Server 2008 R2 Sp1
我在知道你有日历表之前就已经完成了。我用过master.dbo.spt_values生成过去连续十二个月（包括当前）。
declare @ProductNo int
set @ProductNo = 1234
select MONTH(d.date), YEAR(d.date), isnull(t.amnt, 0) as [Units sold] from (
SELECT
YEAR(o.OrderDate) as 'Year',
MONTH(o.OrderDate) as 'Month',
sum(Amount) as amnt,
[ProductNo]
FROM [OrderLine] ol
inner join [Order] o on ol.OrderNo = o.OrderNo
where ProductNo = @ProductNo
group by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
) t
right join (
select dateadd(mm, -number, getdate()) as date
from master.dbo.spt_values
where type = 'p' and number < 12
) d on year(d.date) = t.[year] and month(d.date) = t.[month]
order by YEAR(d.date), MONTH(d.date)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
