select COL1,COL2=(select sum(COL2) from tb where id>-t.id) from tb as t
create table yya (COL1 int, COL2 int)insert into yya select 1, 100 union all select 2, 200 union all select 3, null union all select 4, null union all select 5, null declare @c1 int,@c2 int declare ap scroll cursor for select COL1,COL2 from yya where COL2 is nullopen apfetch first from ap into @c1,@c2 while(@@fetch_status<>-1) begin update a set a.COL2=(select sum(COL2) from yya b where b.COL1<a.COL1) from yya a where a.COL1=@c1
fetch next from ap into @c1,@c2 endclose ap deallocate ap select COL1,COL2 from yya/* COL1 COL2 ----------- ----------- 1 100 2 200 3 300 4 600 5 1200(5 row(s) affected) */
insert into tb(col1,col2) Select distinct col1=(Select top 1 col1 From #AA order by col1 desc )+1,b=(Select Sum(col2) From tb) From tb 或许不是很好,但是能实现楼主的办法。
from tb as t
create table yya
(COL1 int, COL2 int)insert into yya
select 1, 100 union all
select 2, 200 union all
select 3, null union all
select 4, null union all
select 5, null
declare @c1 int,@c2 int
declare ap scroll cursor
for select COL1,COL2 from yya where COL2 is nullopen apfetch first from ap into @c1,@c2
while(@@fetch_status<>-1)
begin
update a
set a.COL2=(select sum(COL2)
from yya b
where b.COL1<a.COL1)
from yya a
where a.COL1=@c1
fetch next from ap into @c1,@c2
endclose ap
deallocate ap
select COL1,COL2 from yya/*
COL1 COL2
----------- -----------
1 100
2 200
3 300
4 600
5 1200(5 row(s) affected)
*/
Select distinct col1=(Select top 1 col1 From #AA order by col1 desc )+1,b=(Select Sum(col2) From tb) From tb
或许不是很好,但是能实现楼主的办法。