SQL Server：计算每月总销售额，包括空余月份

SQL Server：计算每月总销售额，包括空余月份

在知道您有日历表之前，我已经做完了。我已经习惯

master.dbo.spt_values

了连续十二个月（包括当前）。

    declare @ProductNo int    set @ProductNo = 1234select 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)) tright 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)