declare es cursor
for select id from flow_meter
open es
fetch next from es into @eid
while(@@fetch_status=0 )
begin
select @avgarea= avg(area) from households where energy_id=@eid
select @sumTime= sum(rr.electricity-r.electricity) from households h inner join readings_record_temp r on h.id=r.household_id inner join readings_record rr on h.id=rr.household_id where energy_id=@eid
set @olden=(select top 1 energy from flow_meter_history where id=@eid and flag=0 order by date_time desc)
set @Qa=(select energy-@olden from flow_meter where id=@eid) select @oldtime=max(date_time) from flow_meter_history where id=@eid
if(cast(datediff(hour,@oldtime,@date_time) as decimal(18,2))>6)--故障转存
begin
declare @newen decimal(18,2)
set @newen=(select energy from flow_meter where id=@eid)
insert into flow_meter_history (id,energy,date_time,flag) values(@eid,@newen-@olden,cast(@date_time as varchar), 1)
--更新6小时转存一次的临时表
delete from readings_record_temp where household_id in (select household_id from readings_record a,households b where a.household_id=b.id and b.flag=1 and b.energy_id=@eid); insert into readings_record_temp (household_id,date_time,energy,temperature,out_temperature,electricity,relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state)
select household_id,cast(@date_time as varchar),energy,temperature,out_temperature,electricity,relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state
from readings_record a,households b where a.household_id=b.id and b.flag=1 and b.energy_id=@eid; end
else--正常计算
begin
declare nhs cursor
for
select h.id,h.area,h.cop,h.[power],r.date_time,r.electricity,r.energy,rr.electricity,rr.energy,rr.relay_meter,r.temperature from households h inner join readings_record_temp r on h.id=r.household_id inner join readings_record rr on h.id=rr.household_id where h. flag=1 and energy_id=@eid
open nhs
fetch next from nhs into @hid,@area,@cop,@power,@oldtime,@olde,@oldn,@newe,@newn,@relay,@temperature
while(@@fetch_status=0 )
begin
if (@hid=1276)
begin
declare @newent decimal(18,2)
if (@sumTime>0)
begin set @newent=@Qa*@area*(@newe-@olde)/@avgarea/@sumTime +@newn end
else
begin set @newent=@newn end
--更新6小时转存一次的临时表
delete from readings_record_temp where household_id=@hid
insert into readings_record_temp (household_id,date_time,energy,temperature,out_temperature,electricity,relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state)
select household_id,cast(@date_time as varchar),@newent, temperature,out_temperature,@newe, relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state from readings_record where household_id=@hid
end
fetch next from nhs into @hid,@area,@cop,@power,@oldtime,@olde,@oldn,@newe,@newn,@relay,@temperature end
close nhs
deallocate nhs
end
fetch next from es into @eid
end
close es
deallocate es
里面的游标nhs会执行2次,各位帮帮忙啊
for select id from flow_meter
open es
fetch next from es into @eid
while(@@fetch_status=0 )
begin
select @avgarea= avg(area) from households where energy_id=@eid
select @sumTime= sum(rr.electricity-r.electricity) from households h inner join readings_record_temp r on h.id=r.household_id inner join readings_record rr on h.id=rr.household_id where energy_id=@eid
set @olden=(select top 1 energy from flow_meter_history where id=@eid and flag=0 order by date_time desc)
set @Qa=(select energy-@olden from flow_meter where id=@eid) select @oldtime=max(date_time) from flow_meter_history where id=@eid
if(cast(datediff(hour,@oldtime,@date_time) as decimal(18,2))>6)--故障转存
begin
declare @newen decimal(18,2)
set @newen=(select energy from flow_meter where id=@eid)
insert into flow_meter_history (id,energy,date_time,flag) values(@eid,@newen-@olden,cast(@date_time as varchar), 1)
--更新6小时转存一次的临时表
delete from readings_record_temp where household_id in (select household_id from readings_record a,households b where a.household_id=b.id and b.flag=1 and b.energy_id=@eid); insert into readings_record_temp (household_id,date_time,energy,temperature,out_temperature,electricity,relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state)
select household_id,cast(@date_time as varchar),energy,temperature,out_temperature,electricity,relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state
from readings_record a,households b where a.household_id=b.id and b.flag=1 and b.energy_id=@eid; end
else--正常计算
begin
declare nhs cursor
for
select h.id,h.area,h.cop,h.[power],r.date_time,r.electricity,r.energy,rr.electricity,rr.energy,rr.relay_meter,r.temperature from households h inner join readings_record_temp r on h.id=r.household_id inner join readings_record rr on h.id=rr.household_id where h. flag=1 and energy_id=@eid
open nhs
fetch next from nhs into @hid,@area,@cop,@power,@oldtime,@olde,@oldn,@newe,@newn,@relay,@temperature
while(@@fetch_status=0 )
begin
if (@hid=1276)
begin
declare @newent decimal(18,2)
if (@sumTime>0)
begin set @newent=@Qa*@area*(@newe-@olde)/@avgarea/@sumTime +@newn end
else
begin set @newent=@newn end
--更新6小时转存一次的临时表
delete from readings_record_temp where household_id=@hid
insert into readings_record_temp (household_id,date_time,energy,temperature,out_temperature,electricity,relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state)
select household_id,cast(@date_time as varchar),@newent, temperature,out_temperature,@newe, relay_meter,relay_1,relay_2,error1,error2,error3,error4,error5,DI1,DI2,DI3,DI4,DI5,DI6,DI7,DI8,state,in_state,out_state from readings_record where household_id=@hid
end
fetch next from nhs into @hid,@area,@cop,@power,@oldtime,@olde,@oldn,@newe,@newn,@relay,@temperature end
close nhs
deallocate nhs
end
fetch next from es into @eid
end
close es
deallocate es
里面的游标nhs会执行2次,各位帮帮忙啊
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货