declare @temp table (a ...,b ...,u int,v numeric(10,2)) insert @tmp select a,b,count(*) as u,AVG(c) as v from table2update table1 set d=u,e=v from table1 x,table2 y where x.a=y.a and x.b=y.b 两个表对(A,B)加索引。
有笔误:declare @temp table (a ...,b ...,u int,v numeric(10,2)) insert @temp select a,b,count(*) as u,AVG(c) as v from table2update table1 set d=u,e=v from table1 x,@temp y where x.a=y.a and x.b=y.b 两个表对(A,B)加索引。
楼主那样会产生一个全表更新,直接写的话要象这样。UPDATE table1 SET d = tab.avgc from (SELECT AVG(c) avgc,a,b FROM table2 group by a,b ) tab where table1.a = tab.a and table1.b = tab.bUPDATE table1 SET d = tab.countc from (SELECT count(c) countc,a,b FROM table2 group by a,b ) tab where table1.a = tab.a and table1.b = tab.b
为什么要table1的d字段赋两次值,我想应该是Yang_(扬帆破浪) 想的,一个d字段,一个e字段。UPDATE table1 SET d = tab.avgc,e = tab.countc from (SELECT AVG(c) avgc,count(*) countc,a,b FROM table2 group by a,b ) tab where table1.a = tab.a and table1.b = tab.btable2建立索引(a,b),能加快速度。 table1建不建索引无所谓,因为table1是全表更新,建索引了也要进行全表扫描,用不上。
insert @tmp
select a,b,count(*) as u,AVG(c) as v
from table2update table1
set d=u,e=v
from table1 x,table2 y
where x.a=y.a
and x.b=y.b
两个表对(A,B)加索引。
insert @temp
select a,b,count(*) as u,AVG(c) as v
from table2update table1
set d=u,e=v
from table1 x,@temp y
where x.a=y.a
and x.b=y.b
两个表对(A,B)加索引。
SET d = tab.avgc
from (SELECT AVG(c) avgc,a,b FROM table2
group by a,b ) tab
where table1.a = tab.a
and table1.b = tab.bUPDATE table1
SET d = tab.countc
from (SELECT count(c) countc,a,b FROM table2
group by a,b ) tab
where table1.a = tab.a
and table1.b = tab.b
SET d = tab.avgc,e = tab.countc
from (SELECT AVG(c) avgc,count(*) countc,a,b FROM table2
group by a,b ) tab
where table1.a = tab.a and table1.b = tab.btable2建立索引(a,b),能加快速度。
table1建不建索引无所谓,因为table1是全表更新,建索引了也要进行全表扫描,用不上。
可以少了一次扫描。