开始数据
m1 m2 m3
100 10 NULL
100 10 NULL
100 10 NULL
最终数据
m1 m2 m3
100 10 100-10=90
100 10 90+100-10=180
100 10 180+100-10=270
说明:
m3的值,第一行为m1-m2,其他行的均为上一行的m3+本行的m1-本行的m2测试数据if OBJECT_ID('t') is not null
drop table t
go
create table t
(
m1 int,
m2 int,
m3 int
)
go
insert into t values(100,10,null),
(100,10,null),
(100,10,null)
table
m1 m2 m3
100 10 NULL
100 10 NULL
100 10 NULL
最终数据
m1 m2 m3
100 10 100-10=90
100 10 90+100-10=180
100 10 180+100-10=270
说明:
m3的值,第一行为m1-m2,其他行的均为上一行的m3+本行的m1-本行的m2测试数据if OBJECT_ID('t') is not null
drop table t
go
create table t
(
m1 int,
m2 int,
m3 int
)
go
insert into t values(100,10,null),
(100,10,null),
(100,10,null)
table
set @m3 = 0
update t
set @m3 = @m3 + m1 - m2
,m3 - @m3
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2013-01-11 10:32:21
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([m1] int,[m2] int,[m3] int)
insert [test]
select 100,10,null union all
select 100,10,null union all
select 100,10,nullselect * from [test]
go
alter table test add id int identity
go
update test
set [m3]=(select SUM([m1]-[m2]) from test b where test.id>=b.id)alter table test drop column id
select * from test
/*
m1 m2 m3
-----------------------------
100 10 90
100 10 180
100 10 270
*/
set @m3 = 0
update t
set @m3 = @m3 + m1 - m2
,m3 = @m3
已经测试