WHILE (@@FETCH_STATUS = 0)begin
update time_sum
set ncount =tem.aa,
nrate =tem.bb
from (select count(1) aa,sum(time_rate) bb from time_count where @ctime >= time_start and @ctime < time_end) tem
where ctime = @ctime
FETCH NEXT FROM dtime into @ctimeend
update time_sum
set ncount =tem.aa,
nrate =tem.bb
from (select count(1) aa,sum(time_rate) bb from time_count where @ctime >= time_start and @ctime < time_end) tem
where ctime = @ctime
FETCH NEXT FROM dtime into @ctimeend
set ncount = (select count(1) from time_count where time_sum.ctime >= time_start and time_sum.ctime < time_end ),
nrate = (select sum(time_rate) from time_count where time_sum.ctime >= time_start and time_sum.ctime < time_end)
from time_sum
where ctime = time_sum.ctime??
select * into #temp from time_sum update time_sum
set ncount = (select count(1) from #temp c where c.ctime >= a.time_start and ctime < a.time_end ),
nrate = (select sum(time_rate) from #temp d where d.ctime >= a.time_start and d.ctime < a.time_end) from time_sum a,#temp b
where a.ctime = b.ctimeselect * from time_sumdrop table #temp
set ncount = (select count(1) from time_count where ctime >= time_start and ctime < time_end ),
nrate = (select sum(time_rate) from time_count where ctime >= time_start and ctime < time_end)
set ncount = (select count(1) from time_count where time_sum.ctime >= time_start and time_sum.ctime < time_end ),
nrate = (select sum(time_rate) from time_count where time_sum.ctime >= time_start and time_sum.ctime < time_end)
from time_sum
在 time_sub 表建立 ctime 字段上建立索引
time_count 的 time_start 和 time_end 上也建立索引并直接采用下面的语句更新update time_sum set
ncount=(select count(1) from time_count where a.time >=time_start and a.time<time_end)
,nrate=(select sum(time_rate) from time_count where a.ctime >= time_start and a.ctime < time_end)
from time_sum a
update time_sum set
ncount=(select count(1) from time_count where a.ctime >=time_start and a.ctime<time_end)
,nrate=(select sum(time_rate) from time_count where a.ctime >= time_start and a.ctime < time_end)
from time_sum a
from time_sum a inner join (
select a.ctime,count(1) as ncount,sum(b.time_rate) as time_rate
from time_sum a,time_count b
where a.ctime >= time_start and a.ctime < time_end
group by a.ctime) b on a.ctime=b.ctime