--示例--示例数据
create table tb(字段0 varchar(10),字段1 int,字段2 datetime,字段3 int,字段4 int)
insert tb(字段0,字段1,字段2,字段3)
select 'aa',200,'2004/07/10',50
union all select 'aa',200,'2004/07/11',5
union all select 'aa',200,'2004/07/20',23
union all select 'bb',500,'2004/07/10',2
union all select 'bb',500,'2004/07/13',5
union all select 'bb',500,'2004/07/14',10
go--更新字段4
update a set 字段4=(select max(字段1)-sum(字段3) from tb where 字段0=a.字段0 and 字段2<=a.字段2)
from tb a
go--显示更新结果
select * from tb
go--删除测试
drop table tb/*--测试结果字段0 字段1 字段2 字段3 字段4
---------- ----------- ------------------------- ----------- ---------
aa 200 2004-07-10 00:00:00.000 50 150
aa 200 2004-07-11 00:00:00.000 5 145
aa 200 2004-07-20 00:00:00.000 23 122
bb 500 2004-07-10 00:00:00.000 2 498
bb 500 2004-07-13 00:00:00.000 5 493
bb 500 2004-07-14 00:00:00.000 10 483(所影响的行数为 6 行)
--*/
create table tb(字段0 varchar(10),字段1 int,字段2 datetime,字段3 int,字段4 int)
insert tb(字段0,字段1,字段2,字段3)
select 'aa',200,'2004/07/10',50
union all select 'aa',200,'2004/07/11',5
union all select 'aa',200,'2004/07/20',23
union all select 'bb',500,'2004/07/10',2
union all select 'bb',500,'2004/07/13',5
union all select 'bb',500,'2004/07/14',10
go--更新字段4
update a set 字段4=(select max(字段1)-sum(字段3) from tb where 字段0=a.字段0 and 字段2<=a.字段2)
from tb a
go--显示更新结果
select * from tb
go--删除测试
drop table tb/*--测试结果字段0 字段1 字段2 字段3 字段4
---------- ----------- ------------------------- ----------- ---------
aa 200 2004-07-10 00:00:00.000 50 150
aa 200 2004-07-11 00:00:00.000 5 145
aa 200 2004-07-20 00:00:00.000 23 122
bb 500 2004-07-10 00:00:00.000 2 498
bb 500 2004-07-13 00:00:00.000 5 493
bb 500 2004-07-14 00:00:00.000 10 483(所影响的行数为 6 行)
--*/
select 字段0,字段1,字段2,字段3,(select sum(字段3) from table m where
字段0=t.字段0 and 字段2<t.字段2 group by 字段0)
) 字段4
from table t