关于拆分数据
日期：2014-05-18 浏览次数：20371 次
求助关于拆分数据
有一个小时电量表（T_HourEnergy），少量数据如下：
F_MeterID F_StartHour F_StartHour F_Values F_HourInterval
350100A10001001 2011-9-20 11:00 2011-9-20 15:00 1239.2 4
350100A10001001 2011-9-20 20:00 2011-9-20 23:00 185.8 3
350100A10001002 2011-10-13 10:00 2011-10-13 12:00 1048 2
350100A10001002 2011-10-18 08:00 2011-10-18 11:00 1521.6 3
现在想根据开始时间与结束时间的间隔，平均分摊电量值，得到如下数据：
F_MeterID F_StartHour F_StartHour F_Values F_HourInterval
350100A10001001 2011-9-20 11:00 2011-9-20 12:00 309.8 1
350100A10001001 2011-9-20 12:00 2011-9-20 13:00 309.8 1
350100A10001001 2011-9-20 13:00 2011-9-20 14:00 309.8 1
350100A10001001 2011-9-20 14:00 2011-9-20 15:00 309.8 1
350100A10001001 2011-9-20 20:00 2011-9-20 21:00 61.93 1
350100A10001001 2011-9-20 21:00 2011-9-20 22:00 61.93 1
350100A10001001 2011-9-20 22:00 2011-9-20 23:00 61.93 1
350100A10001002 2011-10-13 10:00 2011-10-13 11:00 524 1
350100A10001002 2011-10-13 11:00 2011-10-13 12:00 524 1
350100A10001002 2011-10-18 08:00 2011-10-18 09:00 507.2 1
350100A10001002 2011-10-18 09:00 2011-10-18 10:00 507.2 1
350100A10001002 2011-10-18 10:00 2011-10-18 11:00 507.2 1
其中 309.8 = 1239.2/4，61.93 = 185.8 / 3，其他也类似。
请问用SQL语句怎么实现，请指点，谢谢！
------解决方案--------------------
有一个小时电量表（T_HourEnergy），少量数据如下：
F_MeterID F_StartHour F_StartHour F_Values F_HourInterval
350100A10001001 2011-9-20 11:00 2011-9-20 15:00 1239.2 4
350100A10001001 2011-9-20 20:00 2011-9-20 23:00 185.8 3
350100A10001002 2011-10-13 10:00 2011-10-13 12:00 1048 2
350100A10001002 2011-10-18 08:00 2011-10-18 11:00 1521.6 3
现在想根据开始时间与结束时间的间隔，平均分摊电量值，得到如下数据：
F_MeterID F_StartHour F_StartHour F_Values F_HourInterval
350100A10001001 2011-9-20 11:00 2011-9-20 12:00 309.8 1
350100A10001001 2011-9-20 12:00 2011-9-20 13:00 309.8 1
350100A10001001 2011-9-20 13:00 2011-9-20 14:00 309.8 1
350100A10001001 2011-9-20 14:00 2011-9-20 15:00 309.8 1
350100A10001001 2011-9-20 20:00 2011-9-20 21:00 61.93 1
350100A10001001 2011-9-20 21:00 2011-9-20 22:00 61.93 1
350100A10001001 2011-9-20 22:00 2011-9-20 23:00 61.93 1
350100A10001002 2011-10-13 10:00 2011-10-13 11:00 524 1
350100A10001002 2011-10-13 11:00 2011-10-13 12:00 524 1
350100A10001002 2011-10-18 08:00 2011-10-18 09:00 507.2 1
350100A10001002 2011-10-18 09:00 2011-10-18 10:00 507.2 1
350100A10001002 2011-10-18 10:00 2011-10-18 11:00 507.2 1
其中 309.8 = 1239.2/4，61.93 = 185.8 / 3，其他也类似。
请问用SQL语句怎么实现，请指点，谢谢！
------解决方案--------------------
- SQL code
CREATE TABLE tb (
F_MeterID char(15),
F_StartHour datetime,
F_EndHour datetime,
F_Values numeric(18, 4),
F_HourInterval int
)
INSERT INTO tb
SELECT '350100A10001001', '2011-9-20 11:00', '2011-9-20 15:00', 1239.2, 4 UNION ALL
SELECT '350100A10001001', '2011-9-20 20:00', '2011-9-20 23:00', 185.8, 3 UNION ALL
SELECT '350100A10001002', '2011-10-13 10:00', '2011-10-13 12:00', 1048, 2 UNION ALL
SELECT '350100A10001002', '2011-10-18 08:00', '2011-10-18 11:00', 1521.6, 3
go
select
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 = 1
from tb m,
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) n
where
dateadd(hh,n.num,m.F_StartHour)<=dateadd(hh,-1,m.F_EndHour)
order by m.F_MeterID , F_StartHour
drop 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 1
350100A10001001 2011-09-20 12:00:00.000 2011-09-20 13:00:00.000 309.80 1
350100A10001001 2011-09-20 13:00:00.000 2011-09-20 14:00:00.000 309.80 1
350100A10001001 2011-09-20 14:00:00.000 2011-09-20 15:00:00.000 309.80 1
350100A10001001 2011-09-20 20:00:00.000 2011-09-20 21:00:00.000 61.93 1
350100A10001001 2011-09-20 21:00:00.000 2011-09-20 22:00:00.000 61.93 1
350100A10001001 2011-09-20 22:00:00.000 2011-09-20 23:00:00.000 61.93 1
350100A10001002 2011-10-13 10:00:00.000 2011-10-13 11:00:00.000 524.00 1
350100A10001002 2011-10-13 11:00:00.000 2011-10-13 12:00:00.000 524.00
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
