问：TSQL - 在数据中添加缺失的年月差距？

另一种选择是一个特设的理货表

Declare @OON table (Name varchar(25),YR int,MO int,Cnt int) 
Insert Into @OON values 
('Kelly' , 2014 , 1 , 197), 
('Kelly' , 2014 , 3 , 200), 
('Kelly' , 2014 , 5 , 300), 
('Kelly' , 2015 , 2 , 100), 
('Kelly' , 2015 , 3 , 50), 
('Kelly' , 2015 , 6 , 70) 
Declare @PerBeg date = '2014-01-01' 
Declare @Months int = 24 
Select B.Name 
     ,Yr=Year(A.D) 
     ,MO=Month(A.D) 
     ,Cnt=IsNull(C.Cnt,0) 
From (Select Top (@Months) D=DateAdd(MM,-1+Row_Number() Over (Order By (Select null)),@PerBeg) From master..spt_values) A 
Cross Join (Select Distinct Name from @OON) B 
Left Join @OON C on B.Name=C.Name and Year(A.D)=C.Yr and Month(A.D)=C.MO 

返回

Name Yr  MO Cnt 
Kelly 2014 1 197 
Kelly 2014 2 0 
Kelly 2014 3 200 
Kelly 2014 4 0 
Kelly 2014 5 300 
Kelly 2014 6 0 
Kelly 2014 7 0 
Kelly 2014 8 0 
Kelly 2014 9 0 
Kelly 2014 10 0 
Kelly 2014 11 0 
Kelly 2014 12 0 
Kelly 2015 1 0 
Kelly 2015 2 100 
Kelly 2015 3 50 
Kelly 2015 4 0 
Kelly 2015 5 0 
Kelly 2015 6 70 
Kelly 2015 7 0 
Kelly 2015 8 0 
Kelly 2015 9 0 
Kelly 2015 10 0 
Kelly 2015 11 0 
Kelly 2015 12 0 

你可以有一个永久的日历表，或者你可以逃脱正常的Numbers表格。无论哪种方式，建立一个递归cte是更可取的解决方案。但是，如果是一次性的报告，您可以轻松地摆脱简单的CTE，就像每下面：

;with n as (
    select 1 as n 
    UNION ALL 
    SELECT n + 1 FROM n WHERE n < 20), 
years as (
    select 2013 + n.n as [year] from n where n < 3), 
months as (
    select 0 + n.n as [month] from n where n < 13) 
select r.name, years.[year], months.[month], coalesce(r.Cnt,0) 
from years cross join months 
left join dbo.OON r on years.[year] = r.[YR] and months.[month] = r.[MO] 
order by 1,2,3 

在上面的查询，你不妨用初值为years CTE和数量今年作为参数