• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

问:TSQL – 在数据中添加缺失的年月差距?

sql 来源:jaguin 3次浏览

我有一个要求填写数据中缺失的时间间隔,并填充Cnt列为0的任何间隙。我需要为此使用日历表吗?问:TSQL – 在数据中添加缺失的年月差距?

我的查询结果已经汇总,我只需要添加空白。

select name 
     ,YR 
     ,MO 
     ,[Cnt] 
from dbo.OON 

电流输出:

Name | YR | MO | Cnt 
Kelly | 2014 | 1 | 197 
Kelly | 2014 | 3 | 200 
Kelly | 2014 | 5 | 300 
Kelly | 2015 | 2 | 100 
Kelly | 2015 | 3 | 50 
Kelly | 2015 | 6 | 70 

所需的输出:

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 


===========解决方案如下:

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

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 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)