一个SQL问题,好象有点难...在线给分...

[gopark]

这么个表
t1 t2 t3 t4
1 A1 20 100
2 A1 20 100
3 A2 10 50
4 A2 20 50
5 A1 10 100
6 A2 5 50
想用Update成这样
t1 t2 t3 t4
1 A1 20 80
2 A1 20 60
3 A2 10 40
4 A2 20 20
5 A1 10 50
6 A2 5 15
原来的t4是当前库存,t3是出货量,想把t4更新成剩余库存,只是要按t1的顺序哦...难不难??

[-狙击手-]

update tablea
set t4 = t4 - (select sum(t3)
from tablea
where a.t2 = t2 and t1<= a.tid )
from tablea a

[-狙击手-]

create table t(t1 int,t2 char(2),t3 int,t4 int)
insert t
select 1,'A1',20,100 union all
select 2,'A1',20,100 union all
select 3,'A2',10,50 union all
select 4,'A2',20,50 union all
select 5,'A1',10,100 union all
select 6,'A2',5,50
go
select * from t
update t
set t4 = t4 - (select sum(t3)
from t
where a.t2 = t2 and t1<= a.t1 )
from t a
select * from t

drop table t

/*
t1 t2 t3 t4
----------- ---- ----------- -----------
1 A1 20 100
2 A1 20 100
3 A2 10 50
4 A2 20 50
5 A1 10 100
6 A2 5 50

（所影响的行数为 6 行）


（所影响的行数为 6 行）

t1 t2 t3 t4
----------- ---- ----------- -----------
1 A1 20 80
2 A1 20 60
3 A2 10 40
4 A2 20 20
5 A1 10 50
6 A2 5 15

（所影响的行数为 6 行）
*/

[zhouhaihe]

update table set t4=t4 - (select sum(t3) from table where t2=a.t2 and t1<=a.t1)
from table a

[子陌红尘]

declare @t table(t1 int,t2 varchar(10),t3 int,t4 int)
insert into @t select 1,'A1',20,100
insert into @t select 2,'A1',20,100
insert into @t select 3,'A2',10,50
insert into @t select 4,'A2',20,50
insert into @t select 5,'A1',10,100
insert into @t select 6,'A2',5 ,50

update a
set
t4=a.t4-(select sum(t3) from @t where t2=a.t2 and t1<=a.t1)
from
@t a

select * from @t

/*
t1 t2 t3 t4
----------- ---------- ----------- -----------
1 A1 20 80
2 A1 20 60
3 A2 10 40
4 A2 20 20
5 A1 10 50
6 A2 5 15
*/