# 更新语句继续提问。别嫌我烦。答对者每人可得100分。重复不算。（可用分快没了）

ShapeRock 2003-03-24 11:25:47

id the1 th2 (注意id可以重复）
1 4 10
1 6 15
2 3 5
3 1 3

id sum1 sum2 (sum1为表theA的id<=theB.id的所有the1的累加）
1 0 0 (sum2为表theA的id<=theB.id的所有the2的累加）
2 0 0
3 0 0

id sumNum sumMemory
1 10 25
2 13 30
3 14 33

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)

...全文
50 点赞 收藏 29

29 条回复

ShapeRock 2003-03-25
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

ShapeRock 2003-03-25
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) 已经最快了，没办法了。

Rewiah 2003-03-25

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)

happydreamer 2003-03-25

CrazyFor 2003-03-24
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

ShapeRock 2003-03-24

pengdali 2003-03-24

CrazyFor 2003-03-24

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

ShapeRock 2003-03-24

yonghengdizhen 2003-03-24

yoki 2003-03-24

ShapeRock 2003-03-24

pengdali 2003-03-24

pengdali 2003-03-24
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)

happydreamer 2003-03-24

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

or

update theb
set sum1=(select sum(the1) from thea where id<=theb.id),
sum2=(select sum(the2) from thea where id<=theb.id)

liuyunfeidu 2003-03-24

ShapeRock 2003-03-24
sorry我重新测试
to pengdali(大力) 你的最后那句用时2分50秒。
to CrazyFor(蚂蚁) 你的最后那句用时2分43秒。

ShapeRock 2003-03-24
to ：pengdali(大力)

ShapeRock 2003-03-24
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.id

update theb set sum1=a.th1,sum2=a.th2 from #1 a where a.id=theb.id

select * from theb
drop table #,#1

2.1w+

MS-SQL Server 疑难问题

2003-03-24 11:25