select id,sum(the1),sum(the2) from (select b.id,a.the1,a.the2 from theb b left join thea a on b.id>=a.id)aa group by id
从update语句上我想不出了!看看有没有其他解最优了!
update theB set sum1=bbb.sum1,sum2=bbb.sum2 from TheB left join (select id,sum(the1) as sum1,sum(the2) as sum2 from (select b.id,a.the1,a.the2 from theb b left join thea a on b.id>=a.id)aa group by id)bbb on tehB.id=bbb.id给theA,theB表的ID字段加索引。
try:select a.id,sum(the1) the1,sum(th2) th2 into # from thea a join thea b on a.id<=b.id group by a.id update theb set sum1=a.the1,sum2=a.the2 from # a where a.id=theb.id drop table #
TRY: update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id) from (select a.id,sum(the1) the1,sum(th2) from TheA group by id) TheA
TRY: update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id) from (select a.id,sum(the1) the1,sum(th2) from TheA group by id) TheA
try:select id,sum(the1) the1,sum(th2) th2 into # from thea group by id select b.id,sum(a.the1) th1 ,sum(a.th2) th2 into #1 from # a join # b on a.id<=b.id group by b.idupdate theb set sum1=a.th1,sum2=a.th2 from #1 a where a.id=theb.idselect * from theb drop table #,#1
to : CrazyFor TRY: update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id) from (select a.id,sum(the1) the1,sum(th2) from TheA group by id) TheA 的前半和我说的语句没有区别。后半句有错误。
TRY: select a.id,sum(the1) the1,sum(the2) the2 into # from TheA group by id update theB set sum1=(select sum(the1) from # where #.id<=theB.id),sum2=(select sum(the2) from # where #.id<=theB.id)
to :pengdali(大力) try:select id,sum(the1) the1,sum(th2) th2 into # from thea group by id select b.id,sum(a.the1) th1 ,sum(a.th2) th2 into #1 from # a join # b on a.id<=b.id group by b.idupdate theb set sum1=a.th1,sum2=a.th2 from #1 a where a.id=theb.idselect * from theb drop table #,#1 你这个用时太长,我上了趟厕所。还没更新完。
to :pengdali(大力) 你那个用时2分43秒。
sorry我重新测试 to pengdali(大力) 你的最后那句用时2分50秒。 to CrazyFor(蚂蚁) 你的最后那句用时2分43秒。 但我提问中的那句用时1分51秒。
偶想到的好像就这两个方法 和蚂蚁大力的相同update theB set sum1=bbb.sum1,sum2=bbb.sum2 from TheB left join (select id,sum(the1) as sum1,sum(the2) as sum2 from (select b.id,a.the1,a.the2 from theb b left join thea a on b.id>=a.id)aa group by id)bbb on tehB.id=bbb.idorupdate theb set sum1=(select sum(the1) from thea where id<=theb.id), sum2=(select sum(the2) from thea where id<=theb.id)
Update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id=theB.id) 是最快的写法了!子查询本身就比联结快!
有没有索引? 10000条记录太少,加多两个0试试。 语句还是: Update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id=theB.id)
Update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id) 已经最快了,没办法了。
declare @a bigint,@b bigint set @a=0 set @b=0 Update theB set sum1=cc.aa,sum2=cc.bb from (select id,sum(the1) aa,sum(the2) bb from theA group by id)cc where cc.id=theB.id Update theB set @a=@a+sum1,sum1=@a,@b=@b+sum2,sum2=@b 当theB表的主键是id或有一个索引是id的升序时可以实现。 这里的关键问题是怎么能控制Update的更新顺序。
select id,sum(the1),sum(the2) from (select b.id,a.the1,a.the2 from theb b left join thea a on b.id>=a.id)aa
group by id
(select id,sum(the1) as sum1,sum(the2) as sum2 from (select b.id,a.the1,a.the2 from theb b left join thea a on b.id>=a.id)aa
group by id)bbb on tehB.id=bbb.id给theA,theB表的ID字段加索引。
update theb set sum1=a.the1,sum2=a.the2 from # a where a.id=theb.id
drop table #
update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id)
from (select a.id,sum(the1) the1,sum(th2) from TheA group by id) TheA
update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id)
from (select a.id,sum(the1) the1,sum(th2) from TheA group by id) TheA
select b.id,sum(a.the1) th1 ,sum(a.th2) th2 into #1 from # a join # b on a.id<=b.id group by b.idupdate theb set sum1=a.th1,sum2=a.th2 from #1 a where a.id=theb.idselect * from theb
drop table #,#1
TRY:
update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id<=theB.id)
from (select a.id,sum(the1) the1,sum(th2) from TheA group by id) TheA
的前半和我说的语句没有区别。后半句有错误。
select a.id,sum(the1) the1,sum(the2) the2 into # from TheA group by id
update theB set sum1=(select sum(the1) from # where #.id<=theB.id),sum2=(select sum(the2) from # where #.id<=theB.id)
try:select id,sum(the1) the1,sum(th2) th2 into # from thea group by id
select b.id,sum(a.the1) th1 ,sum(a.th2) th2 into #1 from # a join # b on a.id<=b.id group by b.idupdate theb set sum1=a.th1,sum2=a.th2 from #1 a where a.id=theb.idselect * from theb
drop table #,#1
你这个用时太长,我上了趟厕所。还没更新完。
你那个用时2分43秒。
to pengdali(大力) 你的最后那句用时2分50秒。
to CrazyFor(蚂蚁) 你的最后那句用时2分43秒。
但我提问中的那句用时1分51秒。
(select id,sum(the1) as sum1,sum(the2) as sum2 from (select b.id,a.the1,a.the2 from theb b left join thea a on b.id>=a.id)aa
group by id)bbb on tehB.id=bbb.idorupdate theb
set sum1=(select sum(the1) from thea where id<=theb.id),
sum2=(select sum(the2) from thea where id<=theb.id)
10000条记录太少,加多两个0试试。
语句还是:
Update theB set sum1=(select sum(the1) from theA where theA.id<=theB.id),sum2=(select sum(the2) from theA where theA.id=theB.id)
set @a=0
set @b=0
Update theB set sum1=cc.aa,sum2=cc.bb from (select id,sum(the1) aa,sum(the2) bb from theA group by id)cc where cc.id=theB.id
Update theB set @a=@a+sum1,sum1=@a,@b=@b+sum2,sum2=@b
当theB表的主键是id或有一个索引是id的升序时可以实现。
这里的关键问题是怎么能控制Update的更新顺序。