每半个小时获取一次数据,有时候获取数据不成功,因此在获取数据不成功的情况时,通过查询语句补齐数据,完整的显示一天数据。(注:补齐时如果某一时间间隔没数据它的值置为0)
如下图所示:

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
------解决方案--------------------
用CTE生成日期左联
------解决方案--------------------
方法很多 自己看看吧
------解决方案--------------------
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)
------解决方案--------------------