每天等间隔时间存入数据（如 每隔15分钟），现补全没有存入的数据
如： 2007-1-30 00：15：00 1
2007-1-30 00：45：00 1
2007-1-30 01：00：00 2
2007-1-30 01: 45: 00 1
结果为 2007-1-30 00：30：00 1
2007-1-30 01：15：00 2
2007-1-30 01：30：00 2
[解决办法]
create table T([date] datetime, id int)
insert T select '2007-1-30 00:15:00 ', 1
union all select '2007-1-30 00:45:00 ', 1
union all select '2007-1-30 01:00:00 ', 2
union all select '2007-1-30 01:45:00 ', 1
declare @dt table([date] datetime)
declare @begDate datetime, @endDate datetime
select @begDate=min([date]), @endDate=max([date]) from T
while @begDate <=@endDate
begin
insert @dt([date]) select @begDate
set @begDate=dateadd(minute, 15, @begDate)
end
select A.*,
id=(select top 1 id from T where [date] <A.[date] order by [date] desc)
from @dt as A
left join T on A.[date]=T.[date]
where T.[date] is null
--result
date id
------------------------------------------------------ -----------
2007-01-30 00:30:00.000 1
2007-01-30 01:15:00.000 2
2007-01-30 01:30:00.000 2
(3 row(s) affected)
