表结构 date 字段1 字段2
数据
2012-05-03 02:00:00.000 0.28 1.00
2012-05-03 05:00:00.000 0.28 1.10
2012-05-03 08:00:00.000 2.22 8.00
2012-05-03 11:00:00.000 0.83 3.00
2012-05-03 14:00:00.000 1.11 4.00
2012-05-03 17:00:00.000 2.50 9.00
时间是2、5、8.。。
我要这样的结果
时间是连续的：23456789
34点 的字段1和字段2跟2点的字段1字段2的值相同
2012-05-03 02:00:00.000 0.28 1.00
2012-05-03 03:00:00.000 0.28 1.00
2012-05-03 04:00:00.000 0.28 1.00
2012-05-03 05:00:00.000 0.28 1.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]<[email protected]union 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*/