前后运算的SQL语句如何实现

Question

表的字段为：id,date,value1,value2，value3
如
C001 2010-09-02 45 3
C002 2010-09-02 7687 34
C004 2010-09-02 67 4
C001 2010-09-03 33 6
C002 2010-09-03 33 66
C005 2010-09-03 333 66如何实现 C002 2010-09-03 的 66减去34 （C002 2010-09-02 7687 34）保存到value3（C002 2010-09-03）

Answer 1

update t 
set value3=value2-(select top 1 value2 from tb where id=t.id and date<t.date order by date desc)
from tb t;

Answer 2

create table tb(id varchar(10),date datetime,value1 int,value2 int,value3 int)
insert into tb values('C001', '2010-09-02', 45   ,3  , null)
insert into tb values('C002', '2010-09-02', 7687 ,34, null)
insert into tb values('C004', '2010-09-02', 67   ,4, null)
insert into tb values('C001', '2010-09-03', 33   ,6, null)
insert into tb values('C002', '2010-09-03', 33   ,66, null)
insert into tb values('C005', '2010-09-03', 333  ,66, null)
go--查询
select t.id,date,value1,value2,value3 = value2 - isnull((select top 1 value2 from tb where id = t.id and date < t.date order by date desc),0) from tb t
/*
id         date                                                   value1      value2      value3      
---------- ------------------------------------------------------ ----------- ----------- ----------- 
C001       2010-09-02 00:00:00.000                                45          3           3
C002       2010-09-02 00:00:00.000                                7687        34          34
C004       2010-09-02 00:00:00.000                                67          4           4
C001       2010-09-03 00:00:00.000                                33          6           3
C002       2010-09-03 00:00:00.000                                33          66          32
C005       2010-09-03 00:00:00.000                                333         66          66（所影响的行数为 6 行）
*/--更新
update tb set value3 = value2 - isnull((select top 1 value2 from tb where id = t.id and date < t.date order by date desc),0) from tb t
select * from tb
/*
id         date                                                   value1      value2      value3      
---------- ------------------------------------------------------ ----------- ----------- ----------- 
C001       2010-09-02 00:00:00.000                                45          3           3
C002       2010-09-02 00:00:00.000                                7687        34          34
C004       2010-09-02 00:00:00.000                                67          4           4
C001       2010-09-03 00:00:00.000                                33          6           3
C002       2010-09-03 00:00:00.000                                33          66          32
C005       2010-09-03 00:00:00.000                                333         66          66（所影响的行数为 6 行）
*/drop table tb

Answer 3

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id char(5),
date datetime,
value1 int,
value2 int,
value3 int
)
go
--插入测试数据
insert into tb select 'C001','2010-09-02',45,3,null
union all select 'C002','2010-09-02',7687,34,null
union all select 'C004','2010-09-02',67,4,null
union all select 'C001','2010-09-03',33,6,null
union all select 'C002','2010-09-03',33,66,null
union all select 'C005','2010-09-03',333,66,null
go
--代码实现update t 
set value3=isnull(value2-(select value2 from tb where id=t.id and date<t.date),0)
from tb tselect * from tb
/*测试结果id date value1 value2 value3 
--------------------------------------------------------
C001  2010-09-02 00:00:00.000 45 3 0
C002  2010-09-02 00:00:00.000 7687 34 0
C004  2010-09-02 00:00:00.000 67 4 0
C001  2010-09-03 00:00:00.000 33 6 3
C002  2010-09-03 00:00:00.000 33 66 32
C005  2010-09-03 00:00:00.000 333 66 0(6 行受影响)
*/

Answer 4

按日期递减可以用下语句，我今天问过类似问题．Select *, Value1 - (Select Sum(Value1) from TTable t Where t.ID = TTable.ID and t.DDate < TTable.DDate ) as Value3 from TTable
Order by ID, DDate如果不是递减，要改Value - (....)

Answer 5

--假设是减去之前最近的日期的值,设表名为t
update t
set value3=value2-(select top 1 value2 from t as a where date<t.date and id=t.id order by date desc)