算法提问

[zq200487731]

请问下这个算法怎么提升速度？

select identity(int,1,1) as  pid,staff,staffid,kccode,org,fxid into #authors from T_SecondZb where fxid=@fxID
set @Count=@@rowcount 
create index idx_stusname on #authors (pid)
while @Count>0  
begin
select top 1 @org=org,@staff=staff,@kcCode=kccode,@staffid=staffid from   #authors   where   pid=@Count
/*select @jsnr=sum(jxnr)/count(*),
     @jsff=sum(jxff)/count(*) ,
     @jxtd=sum(jxtd)/count(*), 
     @jsxg=sum(jxxg)/count(*), 
     @yrqk=sum(yrqk)/count(*) 
from T_Defen  
    join T_Pjvalue on T_Pjvalue.pjid=T_defen.pjid where T_Defen.staff=@staff and T_Defen.kccode=@kccode and T_Pjvalue.fxjgid=@fxid*/
insert into T_firstzb(staff,staffid,kccode,org,xqid,fxid) values(@staff,@staffid,@kccode,@org,@xqid,@fxid)
set @Count=@Count-1
end

我现在想这样做，可是好像不对？

update T_firstzb set jcscore=b.jxnr,jfscore=b.jxff,jascore=b.jxtd,jrscore=b.jxxg,jescore=b.yrqk from  T_firstzb 
inner join  #authors a 
on  T_firstzb .[staff] =  a.[staff] and a.[kccode]=t_firstzb.kccode
left join (select sum(jxnr)/count(*) as jxnr,sum(jxff)/count(*) as jxff, sum(jxtd)/count(*) as jxtd,sum(jxxg)/count(*) as jxxg,sum(yrqk)/count(*) as yrqk,t_defen.staff,t_defen.kccode from T_Defen  join T_Pjvalue on T_Pjvalue.pjid=T_defen.pjid where  T_Pjvalue.fxjgid=@fxID) b on b.staff=a.staff and a.kccode=b.kccode where T_firstzb.fxid=@fxid and  T_firstzb .[staff] =  a.[staff] and a.[kccode]=t_firstzb.kccode

[zq200487731]

高手呢？请赖看看啊

[-晴天]

看了半天,没看出来这里有什么算法...
试试:

insert into T_firstzb(staff,staffid,kccode,org,xqid,fxid) select staff,staffid,kccode,org,@xqid,fxid where fxid=@fxid

[-晴天]

少写一个表名:

insert into T_firstzb(staff,staffid,kccode,org,xqid,fxid) select staff,staffid,kccode,org,@xqid,fxid from T_SecondZb where fxid=@fxid

[dobear_0922]

update T_firstzb 
set jcscore=b.jxnr,jfscore=b.jxff,jascore=b.jxtd,jrscore=b.jxxg,jescore=b.yrqk 
from  T_firstzb 
inner join  #authors a 
on  T_firstzb .[staff] =  a.[staff] and a.[kccode]=t_firstzb.kccode
left join 
(select sum(jxnr)/count(*) as jxnr,sum(jxff)/count(*) as jxff, sum(jxtd)/count(*) as jxtd,sum(jxxg)/count(*) as jxxg,sum(yrqk)/count(*) as yrqk,t_defen.staff,t_defen.kccode 
	from T_Defen  join T_Pjvalue on T_Pjvalue.pjid=T_defen.pjid 
	where  T_Pjvalue.fxjgid=@fxID
	group by t_defen.staff,t_defen.kccode) b 
on b.staff=a.staff and a.kccode=b.kccode 
where T_firstzb.fxid=@fxid

[linguojin11]

；LG。。

[dobear_0922]

好长，看看先

[dobear_0922]

第一段SQL没必要用临时表和循环

[水族杰纶]

if exists(select [staff],staffid,kccode,org,fxid from T_SecondZb where fxid=@fxID)
  insert  T_firstzb([staff],staffid,kccode,org,xqid,fxid) select [staff],staffid,kccode,org,@xqid,fxid from T_SecondZb where fxid=@fxID

[ws_hgo]

先看下

[zq200487731]

那请问这段怎么改》？

update T_firstzb set jcscore=b.jxnr,jfscore=b.jxff,jascore=b.jxtd,jrscore=b.jxxg,jescore=b.yrqk from  T_firstzb 
inner join  #authors a 
on  T_firstzb .[staff] =  a.[staff] and a.[kccode]=t_firstzb.kccode
left join (select sum(jxnr)/count(*) as jxnr,sum(jxff)/count(*) as jxff, sum(jxtd)/count(*) as jxtd,sum(jxxg)/count(*) as jxxg,sum(yrqk)/count(*) as yrqk,t_defen.staff,t_defen.kccode from T_Defen  join T_Pjvalue