源表:
num1 num2 date
100 300 2008-4-2
200 500 2008-4-3
300 800 2008-4-4
结果集:
num1 num2 date
100 300 2008-4-2
300 800 2008-4-3
600 1600 2008-4-4 解决方法一:create table #T (num1 int,num2 int,date datetime)
insert into #T
select 100,300,'2008-4-2' union all
select 200,500,'2008-4-3' union all
select 300,800,'2008-4-4'select
num1=(select sum(num1) from #T where date<=t.date),
num2=(select sum(num2) from #T where date<=t.date),
date
from #T as tdrop table #T解决方法二:create table #tb(num1 int,num2 int,date datetime)
insert #tb select 100,300,'2008-4-2'
insert #tb select 200,500,'2008-4-3'
insert #tb select 300,800,'2008-4-4'
insert #tb select 400,1000,'2008-4-4'declare @num1 int,@num2 int
select @num1 = 0,@num2=0
update #tb set
num1 = @num1,
num2 = @num2,
@num1= @num1+num1,
@num2= @num2+num2select * from #tb
drop table #tb
drop table tb
go
create table tb(col1 int,col2 int)
insert into tb select 1,5
insert into tb select 2,6
insert into tb select 3,7
insert into tb select 4,8update tb set col2=(select sum(col2) from tb where col1<=t.col1) from tb tselect * from tb
1 5
2 11
3 18
4 26
set nocount on;
declare @T table([Col1] int,x int)
Insert @T
select 2,5 union all
select 9,12 union all
select 8,6 union all
select 7,8
;with t as
(Select * ,1 as col from @T)
,t2 as
(select [Col1],[Col2]=x,row_number()over(order by col)col from T)
select
col1=(select sum(col1) from t2 where col<=a.col),
col2=(select sum(col2) from t2 where col<=a.col)
from
t2 a
col1 col2
----------- -----------
2 5
11 17
19 23
26 31
set nocount on;
declare @T table([Col1] int,x int)
Insert @T
select 2,5 union all
select 9,12 union all
select 8,6 union all
select 7,8
select *,col=identity(int,1,1) into # from @Tselect
col1=(select sum(col1) from # where col<=a.col),
x=(select sum(x) from # where col<=a.col)
from
# a/*
col1 x
----------- -----------
2 5
11 17
19 23
26 31*/
insert into #tb select * from 表declare @num1 int,@num2 int
select @num1 = 0,@num2=0
update #tb set
num1 = @num1,
num2 = @num2,
@num1= @num1+num1,
@num2= @num2+num2select * from #tb
drop table #tb