Update theA set sum1=theB.aa,sum2=theB.bb
from (Select sum(the1) aa,sum(the2) bb,id from theA group by id) theB
where theB.id=thea.id
from (Select sum(the1) aa,sum(the2) bb,id from theA group by id) theB
where theB.id=thea.id
但应改为 UPDATE QQ SET 。。
Update qq set sum1=theB.aa,sum2=theB.bb from theA qq,(Select sum(the1) aa,sum(the2) bb from theA where id<=qq.id) theB
仍然错误 ,列前缀 'qq' 与查询中所用的表名或别名不匹配。
你的第一个回答也不对。
Update theA set sum1=(select sum(the1) from theA where id<qq.id),sum2=(select sum(the2) from theA where id<qq.id) from theA qq
但对每一个字段都进行一个查询我觉得效率不高。
Update theA set sum1=(select sum(the1) from theA where id<qq.id),sum2=(select sum(the2) from theA where id<qq.id) from theA qq
效率高的我会别开贴给100分。最好能改正我提问时的错误。
theA where id<aa.id))+the1,sum2=(select isNULL(sum2,0) from theA where id=select max(id) from theA where id<aa.id))+the2 from theA aa
这种的写出来不给分。
Update theA set sum1=theB.aa,sum2=theB.bb
from
(
select l.id,sum(r.the1) aa,sum(r.the2) bb
from theA l join theA r on l.id>=r.id
group by l.id) theB
where theA.id=theB.id
-----------------------1 2 2 2 2
2 3 1 5 3
3 4 3 9 6
set @a=0
set @b=0update theA set @a=@a+isnull(the1,0),sum1=@a update theA set @b=@b+isnull(the2,0),sum2=@b