求助关于拆分数据
有一个小时电量表（T_HourEnergy），少量数据如下：
F_MeterIDF_StartHourF_StartHourF_ValuesF_HourInterval
350100A10001001 2011-9-20 11:002011-9-20 15:001239.24
350100A10001001 2011-9-20 20:002011-9-20 23:00185.83
350100A10001002 2011-10-13 10:002011-10-13 12:0010482
350100A10001002 2011-10-18 08:002011-10-18 11:001521.63
现在想根据开始时间与结束时间的间隔，平均分摊电量值，得到如下数据：
F_MeterIDF_StartHourF_StartHourF_ValuesF_HourInterval
350100A10001001 2011-9-20 11:002011-9-20 12:00309.81
350100A10001001 2011-9-20 12:002011-9-20 13:00309.81
350100A10001001 2011-9-20 13:002011-9-20 14:00309.81
350100A10001001 2011-9-20 14:002011-9-20 15:00309.81
350100A10001001 2011-9-20 20:002011-9-20 21:0061.931
350100A10001001 2011-9-20 21:002011-9-20 22:0061.931
350100A10001001 2011-9-20 22:002011-9-20 23:0061.931
350100A10001002 2011-10-13 10:002011-10-13 11:005241
350100A10001002 2011-10-13 11:002011-10-13 12:005241
350100A10001002 2011-10-18 08:002011-10-18 09:00507.21
350100A10001002 2011-10-18 09:002011-10-18 10:00507.21
350100A10001002 2011-10-18 10:002011-10-18 11:00507.21
其中 309.8 = 1239.2/4，61.93 = 185.8 / 3，其他也类似。
请问用SQL语句怎么实现，请指点，谢谢！
[解决办法]
CREATE TABLE tb (F_MeterID char(15), F_StartHour datetime, F_EndHour datetime, F_Values numeric(18, 4), F_HourInterval int)INSERT INTO tbSELECT '350100A10001001', '2011-9-20 11:00', '2011-9-20 15:00', 1239.2, 4 UNION ALLSELECT '350100A10001001', '2011-9-20 20:00', '2011-9-20 23:00', 185.8, 3 UNION ALLSELECT '350100A10001002', '2011-10-13 10:00', '2011-10-13 12:00', 1048, 2 UNION ALLSELECT '350100A10001002', '2011-10-18 08:00', '2011-10-18 11:00', 1521.6, 3goselect F_MeterID , dateadd(hh,n.num,m.F_StartHour) F_StartHour , dateadd(hh,n.num+1,m.F_StartHour) F_StartHour , cast(F_Values / F_HourInterval as decimal(18,2)) F_Values , F_HourInterval = 1from tb m, (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) nwhere dateadd(hh,n.num,m.F_StartHour)<=dateadd(hh,-1,m.F_EndHour)order by m.F_MeterID , F_StartHourdrop table tb/*F_MeterID F_StartHour F_StartHour F_Values F_HourInterval --------------- ------------------------------------------------------ ------------------------------------------------------ -------------------- -------------- 350100A10001001 2011-09-20 11:00:00.000 2011-09-20 12:00:00.000 309.80 1350100A10001001 2011-09-20 12:00:00.000 2011-09-20 13:00:00.000 309.80 1350100A10001001 2011-09-20 13:00:00.000 2011-09-20 14:00:00.000 309.80 1350100A10001001 2011-09-20 14:00:00.000 2011-09-20 15:00:00.000 309.80 1350100A10001001 2011-09-20 20:00:00.000 2011-09-20 21:00:00.000 61.93 1350100A10001001 2011-09-20 21:00:00.000 2011-09-20 22:00:00.000 61.93 1350100A10001001 2011-09-20 22:00:00.000 2011-09-20 23:00:00.000 61.93 1350100A10001002 2011-10-13 10:00:00.000 2011-10-13 11:00:00.000 524.00 1350100A10001002 2011-10-13 11:00:00.000 2011-10-13 12:00:00.000 524.00 1350100A10001002 2011-10-18 08:00:00.000 2011-10-18 09:00:00.000 507.20 1350100A10001002 2011-10-18 09:00:00.000 2011-10-18 10:00:00.000 507.20 1350100A10001002 2011-10-18 10:00:00.000 2011-10-18 11:00:00.000 507.20 1（所影响的行数为 12 行）*/
[解决办法]
IF OBJECT_ID('[T_HourEnergy]') IS NOT NULL DROP TABLE [T_HourEnergy]GOCREATE TABLE [T_HourEnergy] (F_MeterID char(15), F_StartHour datetime, F_EndHour datetime, F_Values numeric(18, 4), F_HourInterval int)INSERT INTO [T_HourEnergy]SELECT '350100A10001001', '2011-9-20 11:00', '2011-9-20 15:00', 1239.2, 4 UNION ALLSELECT '350100A10001001', '2011-9-20 20:00', '2011-9-20 23:00', 185.8, 3 UNION ALLSELECT '350100A10001002', '2011-10-13 10:00', '2011-10-13 12:00', 1048, 2 UNION ALLSELECT '350100A10001002', '2011-10-18 08:00', '2011-10-18 11:00', 1521.6, 3select a.F_MeterID,dateadd(hour,b.number,a.F_StartHour) as F_StartHour,dateadd(hour,b.number+1,a.F_StartHour) as F_EndHour,cast(F_Values/F_HourInterval as decimal(18,2)) as F_Values,'1' as F_HourInterval from [T_HourEnergy] a left join master..spt_values b on b.type='p' and a.F_HourInterval>b.number/*F_MeterID F_StartHour F_EndHour F_Values F_HourInterval--------------- ----------------------- ----------------------- ------------- --------------350100A10001001 2011-09-20 11:00:00.000 2011-09-20 12:00:00.000 309.80 1350100A10001001 2011-09-20 12:00:00.000 2011-09-20 13:00:00.000 309.80 1350100A10001001 2011-09-20 13:00:00.000 2011-09-20 14:00:00.000 309.80 1350100A10001001 2011-09-20 14:00:00.000 2011-09-20 15:00:00.000 309.80 1350100A10001001 2011-09-20 20:00:00.000 2011-09-20 21:00:00.000 61.93 1350100A10001001 2011-09-20 21:00:00.000 2011-09-20 22:00:00.000 61.93 1350100A10001001 2011-09-20 22:00:00.000 2011-09-20 23:00:00.000 61.93 1350100A10001002 2011-10-13 10:00:00.000 2011-10-13 11:00:00.000 524.00 1350100A10001002 2011-10-13 11:00:00.000 2011-10-13 12:00:00.000 524.00 1350100A10001002 2011-10-18 08:00:00.000 2011-10-18 09:00:00.000 507.20 1350100A10001002 2011-10-18 09:00:00.000 2011-10-18 10:00:00.000 507.20 1350100A10001002 2011-10-18 10:00:00.000 2011-10-18 11:00:00.000 507.20 1*/