create table T(rectime datetime) insert T select '2006-12-22 16:44:18.000' union all select '2006-12-22 16:44:17.000' union all select '2006-12-22 16:44:14.000' union all select '2006-12-22 16:44:13.000' union all select '2006-12-22 16:44:11.000' union all select '2006-12-22 16:44:10.000' declare @tb table(rectime datetime)declare @beg_DT datetime, @end_DT datetime select @end_DT=max(rectime) from T select @beg_DT=min(rectime) from T while @beg_DT<=@end_DT begin if not exists(select * from T where rectime=@beg_DT) insert @tb select @beg_DT set @beg_DT=dateadd(ss, 1, @beg_DT) endselect * from @tb--result rectime ------------------------------------------------------ 2006-12-22 16:44:12.000 2006-12-22 16:44:15.000 2006-12-22 16:44:16.000(3 row(s) affected)
比如我的表里面有如下数据:
rectime
------------------------------------------------------
2006-12-22 16:44:18.000
2006-12-22 16:44:17.000
2006-12-22 16:44:14.000
2006-12-22 16:44:13.000
2006-12-22 16:44:11.000
2006-12-22 16:44:10.000要求查询的结果如下:(查询的时间范围在查询是指定)
2006-12-22 16:44:16.000
2006-12-22 16:44:15.000
2006-12-22 16:44:12.000
insert T select '2006-12-22 16:44:18.000'
union all select '2006-12-22 16:44:17.000'
union all select '2006-12-22 16:44:14.000'
union all select '2006-12-22 16:44:13.000'
union all select '2006-12-22 16:44:11.000'
union all select '2006-12-22 16:44:10.000'
declare @tb table(rectime datetime)declare @beg_DT datetime, @end_DT datetime
select @end_DT=max(rectime) from T
select @beg_DT=min(rectime) from T
while @beg_DT<=@end_DT
begin
if not exists(select * from T where rectime=@beg_DT)
insert @tb select @beg_DT set @beg_DT=dateadd(ss, 1, @beg_DT)
endselect * from @tb--result
rectime
------------------------------------------------------
2006-12-22 16:44:12.000
2006-12-22 16:44:15.000
2006-12-22 16:44:16.000(3 row(s) affected)
感谢marco08(天道酬勤)再问一下,
@tb是不是虚拟的表?
我打算把上面的代码写成存储过程,多次调用之后那个@tb会不会自动释放内存呀?然后,效率能不能再提高呢?
我这个data表有100多万条记录的,并且每天的数据都会增加很多条