sql查补缺失数据，有兴趣的看一下
表结构 date 字段1 字段2
数据
2012-05-03 02:00:00.0000.281.00
2012-05-03 05:00:00.0000.281.10
2012-05-03 08:00:00.0002.228.00
2012-05-03 11:00:00.0000.833.00
2012-05-03 14:00:00.0001.114.00
2012-05-03 17:00:00.0002.509.00
时间是2、5、8.。。
我要这样的结果
时间是连续的：23456789
34点 的字段1和字段2跟2点的字段1字段2的值相同
2012-05-03 02:00:00.0000.281.00
2012-05-03 03:00:00.0000.281.00
2012-05-03 04:00:00.0000.281.00
2012-05-03 05:00:00.0000.281.10
如果写sql语句？

[解决办法]

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([date] datetime,[字段1] numeric(3,2),[字段2] numeric(3,2))insert [test]select '2012-05-03 02:00:00.000',0.28,1.00 union allselect '2012-05-03 05:00:00.000',0.28,1.10 union allselect '2012-05-03 08:00:00.000',2.22,8.00 union allselect '2012-05-03 11:00:00.000',0.83,3.00 union allselect '2012-05-03 14:00:00.000',1.11,4.00 union allselect '2012-05-03 17:00:00.000',2.50,9.00godeclare @date datetimeset @date='2012-05-03 08:00:00.000';with tas(select * from [test] where [date]<=@dateunion allselect dateadd(HH,1,a.[date]),ISNULL([字段1],0.28) [字段1],ISNULL([字段2],1.00) [字段2] from t awhere not exists(select * from [test] bwhere b.[date]=DATEADD(HH,1,a.[date]))and a.[date]<@date)select *from t order by [date]/*date    字段1    字段22012-05-03 02:00:00.000    0.28    1.002012-05-03 03:00:00.000    0.28    1.002012-05-03 04:00:00.000    0.28    1.002012-05-03 05:00:00.000    0.28    1.102012-05-03 06:00:00.000    0.28    1.102012-05-03 07:00:00.000    0.28    1.102012-05-03 08:00:00.000    2.22    8.00*/