补齐间隔一天的数据
create table tb
(
A nvarchar(50),
B int
)
insert into tb
select '2012/12/03 16:00:00','12' union
select '2012/12/03 16:30:00','22' union
select '2012/12/03 17:00:00','21' union
select '2012/12/03 17:30:00','22' union
select '2012/12/03 18:00:00','21' union
select '2012/12/03 18:30:00','22' union
select '2012/12/03 19:00:00','25' union
select '2012/12/03 19:30:00','27' union
select '2012/12/03 23:00:00','28' union
select '2012/12/03 23:30:00','22' union
select '2012/12/04 00:30:00','22' union
select '2012/12/04 01:00:00','22' union
select '2012/12/04 01:30:00','22' union
select '2012/12/04 02:00:00','28' union
select '2012/12/04 02:30:00','22' union
select '2012/12/04 03:00:00','22' union
select '2012/12/04 03:30:00','22' union
select '2012/12/04 04:00:00','22' union
select '2012/12/04 04:30:00','22' union
select '2012/12/04 05:00:00','22'
select * from tb
create table tb
(
A nvarchar(50),
B int
)
insert into tb
select '2012/12/03 16:00:00','12' union
select '2012/12/03 16:30:00','22' union
select '2012/12/03 17:00:00','21' union
select '2012/12/03 17:30:00','22' union
select '2012/12/03 18:00:00','21' union
select '2012/12/03 18:30:00','22' union
select '2012/12/03 19:00:00','25' union
select '2012/12/03 19:30:00','27' union
select '2012/12/03 23:00:00','28' union
select '2012/12/03 23:30:00','22' union
select '2012/12/04 00:30:00','22' union
select '2012/12/04 01:00:00','22' union
select '2012/12/04 01:30:00','22' union
select '2012/12/04 02:00:00','28' union
select '2012/12/04 02:30:00','22' union
select '2012/12/04 03:00:00','22' union
select '2012/12/04 03:30:00','22' union
select '2012/12/04 04:00:00','22' union
select '2012/12/04 04:30:00','22' union
select '2012/12/04 05:00:00','22'
--select * from tb
go
declare @dt1 datetime,@dt2 datetime
select @dt1=min(A),@dt2=max(A) from tb
;with a
as
(
select @dt1 as dt
union all
select dt from a where dt<@dt2
)
insert into B
select dt,0
from a
left join tb as b on a.A=b.A
where b.A is null
option(MAXRECURSION 0)
