sqlserver 数据区间补全

数字补全

declare @tb table (id int)
insert into @tb
select 10
union
select 100
union
select 1000

select id,nn,(case when id=nn then '存在' else '补全' end) 
from @tb a
cross apply(
	select number+isnull((select top 1 id from @tb where id<a.id order by id desc),0)+1 as nn
	from master..spt_values where type='p'
	and number<a.id-isnull((select top 1 id from @tb where id<a.id order by id desc),0)
) c
order by nn

使用 master..spt_values 常量表进行数字补全，需要注意的是spt_values中数字常量从0到2047，超过这个范围的数字是跳跃的，不再是连续的数字了，需要其他方式进行补全

日期补全

declare @tb table(dt date)
insert into @tb
select '2015-1-1'
union
select '2016-4-4'

select *,(case when dt=nn then '存在' else '补全' end) 
from @