求助: 如何将没有销售额的月份填补为0

Question

数据库是NorthWind select Year(Orders.OrderDate)as SalesYear,Month(Orders.OrderDate) as SalesMonth,
Products.ProductName, sum([Order Details].Quantity*[Order Details].UnitPrice) as Amount
from Orders 
left join [Order Details]
on Orders.OrderID=[Order Details].OrderID 
left join Products 
on [Order Details].ProductID=Products.ProductID 
where ProductName='Tofu' 
group by Year(Orders.OrderDate),Month(Orders.OrderDate),Products.ProductName
order by SalesYear,SalesMonth 查询结果如下：SalesYear   SalesMonth ProductName           Amount
1996    7          Tofu                    167.4
1996    10          Tofu                    353.4
1996    12          Tofu                    1060.2
1997    1          Tofu                    1246.2
1997    3          Tofu                    223.2
1997    4          Tofu                    1627.5
1997    6          Tofu                    1302
1997    8          Tofu                    558
1997    9          Tofu                    767.25
1997    11          Tofu                    488.25
1997    12          Tofu                    348.75
1998     5          Tofu                    488.25我希望将1996年7月开始到1998年5月之前， 任何一个月没有销售记录的， 填充为0.请高手指点。

Answer 1

先构造一个1996年7月开始到1998年5月的列表，再left join

Answer 2

先构造一个1996年7月开始到1998年5月的列表，再left join 只能解决一次， 一旦年份月份有所变化， 就得重新构建列表 清问如何能够动态解决这个问题呢？

Answer 3

declare @sdt varchar(10),@edt varchar(10)
select @sdt='1996-07',@edt='1998-05'select convert(varchar(7),dateadd(mm,number,@sdt+'-01'),120) as 月份
from master..spt_values
where type='P'
and dateadd(mm,number,@sdt+'-01')<=@edt+'-01'
/**
月份
-------
1996-07
1996-08
1996-09
1996-10
1996-11
1996-12
1997-01
1997-02
1997-03
1997-04
1997-05
1997-06
1997-07
1997-08
1997-09
1997-10
1997-11
1997-12
1998-01
1998-02
1998-03
1998-04
1998-05(23 行受影响)
**/

Answer 4

select year(dateadd(mm,number,'1996-07-01')) as  SalesYear,
       month(dateadd(mm,number,'1996-07-01')) as SalesMonth
from master..spt_values
where type='p' and dateadd(mm,number,'1996-07-01')<='1998-05-01'用上面的子查询去left join你的语句

Answer 5

--2005:
;with c1 as(
select   Year(Orders.OrderDate)as   SalesYear,Month(Orders.OrderDate)   as   SalesMonth, 
Products.ProductName,   sum([Order   Details].Quantity*[Order   Details].UnitPrice)   as   Amount 
from   Orders   
left   join   [Order   Details] 
on   Orders.OrderID=[Order   Details].OrderID   
left   join   Products   
on   [Order   Details].ProductID=Products.ProductID   
where   ProductName= 'Tofu '   
group   by   Year(Orders.OrderDate),Month(Orders.OrderDate),Products.ProductName 
order   by   SalesYear,SalesMonth   
);c2 as(
select dateadd(m,number,'1996-07-01')dt from master..spt_values where type='p' and dateadd(m,number,'1996-07-01')<='1998-05-01'
)select * from c1
union all
select year(dt),month(dt),null,0 from c2 a where not exists(select 1 from c1 where year(a.dt)=salesyear and month(dt)=a.salesmonth)

Answer 6

--2005:
;with c1 as(
select   Year(Orders.OrderDate)as   SalesYear,Month(Orders.OrderDate)   as   SalesMonth, 
Products.ProductName,   sum([Order   Details].Quantity*[Order   Details].UnitPrice)   as   Amount 
from   Orders   
left   join   [Order   Details] 
on   Orders.OrderID=[Order   Details].OrderID   
left   join   Products   
on   [Order   Details].ProductID=Products.ProductID   
where   ProductName= 'Tofu '   
group   by   Year(Orders.OrderDate),Month(Orders.OrderDate),Products.ProductName 
order   by   SalesYear,SalesMonth   
);c2 as(
select dateadd(m,number,'1996-07-01')dt from master..spt_values where type='p' and dateadd(m,number,'1996-07-01')<='1998-05-01'
)select * from c1
union all
select year(dt),month(dt),null,0 from c2 a where not exists(select 1 from c1 where year(a.dt)=salesyear and month(dt)=a.salesmonth)
执行出错