表a
id Date Name in money
1 2007-4-11 abc 200 100
2 2007-4-11 abc 100 200
3 2007-4-11 abc -500 -300
4 2007-4-11 abc 100 -200
5 <NULL> <NULL> -100 -200
6 2007-4-11 def -200 500
7 2007-4-11 def 100 600
8 2007-4-11 def -400 200
9 <NULL> <NULL> -500 200in那一列表示资金流动情况,money那一列表示期末资金余额。当前余额加上下一笔交易的资金流动,就会得出下一笔的余额,如id1记录余额为100,id2有100资金流入,那么id2中的余额就是100+100=200,id3有500资金流出,那么id3中的余额就是200-500=-300。date和name都是null值的那一行是对前面几条的合计,如id5的记录,资金流动情况是200+100-500+100=-100,期末资金余额即最后一笔记录(id4)的余额-200现在想把这个表的数据更新一下,把null更新成相应的值,如下
id Date Name in money
1 2007-4-11 abc 200 100
2 2007-4-11 abc 100 200
3 2007-4-11 abc -500 -300
4 2007-4-11 abc 100 -200
5 2007-4-11 abc合计 -100 -200
6 2007-4-11 def -200 500
7 2007-4-11 def 100 600
8 2007-4-11 def -400 200
9 2007-4-11 def合计 -500 200简单的来说,可以不必理会数据间的关系,只是把null的数据补充上数值。其中,date值为其上一条的值,name的值为其上一条的值再加上“合计”两字
id Date Name in money
1 2007-4-11 abc 200 100
2 2007-4-11 abc 100 200
3 2007-4-11 abc -500 -300
4 2007-4-11 abc 100 -200
5 <NULL> <NULL> -100 -200
6 2007-4-11 def -200 500
7 2007-4-11 def 100 600
8 2007-4-11 def -400 200
9 <NULL> <NULL> -500 200in那一列表示资金流动情况,money那一列表示期末资金余额。当前余额加上下一笔交易的资金流动,就会得出下一笔的余额,如id1记录余额为100,id2有100资金流入,那么id2中的余额就是100+100=200,id3有500资金流出,那么id3中的余额就是200-500=-300。date和name都是null值的那一行是对前面几条的合计,如id5的记录,资金流动情况是200+100-500+100=-100,期末资金余额即最后一笔记录(id4)的余额-200现在想把这个表的数据更新一下,把null更新成相应的值,如下
id Date Name in money
1 2007-4-11 abc 200 100
2 2007-4-11 abc 100 200
3 2007-4-11 abc -500 -300
4 2007-4-11 abc 100 -200
5 2007-4-11 abc合计 -100 -200
6 2007-4-11 def -200 500
7 2007-4-11 def 100 600
8 2007-4-11 def -400 200
9 2007-4-11 def合计 -500 200简单的来说,可以不必理会数据间的关系,只是把null的数据补充上数值。其中,date值为其上一条的值,name的值为其上一条的值再加上“合计”两字
set date=b.date,name=b.name
from 表a a left join 表a b
on b.id=(select max(id) from 表a where id<a.id)
where a.date is null
SET Date= (SELECT TOP 1 DATE FROM tableA b where a.id+1=b.id),
SET Name= (SELECT TOP 1 Name FROM tableA b where a.id+1=b.id)+N'合计'
from tableA a
where Date is null
set date=b.date,name=b.name+'合计'
from 表a a left join 表a b
on b.id=(select max(id) from 表a where id<a.id)
where a.date is null
id int , Date datetime , Name nvarchar(20) , [in] numeric(10,4) , money numeric(10,4))
insert into tableA
select 1, '2007-4-11' , 'abc' , 200 , 100
union select 2, '2007-4-11' , 'abc' , 100 , 200
union select 3, '2007-4-11' , 'abc' , -500 , -300
union select 4, '2007-4-11' , 'abc' , 100 , -200
union select 5, NULL , NULL , -100 , -200
union select 6, '2007-4-11' , 'def' , -200 , 500
union select 7, '2007-4-11' , 'def' , 100 , 600
union select 8, '2007-4-11' , 'def' , -400 , 200
union select 9, NULL , NULL , -500 , 200UPDATE tableA
SET Date= (SELECT TOP 1 DATE FROM tableA b where a.id=b.id+1),
Name= (SELECT TOP 1 Name FROM tableA b where a.id=b.id+1)+N'合计'
from tableA a
where Date is null
2 2007-04-11 00:00:00.000 abc 100.0000 200.0000
3 2007-04-11 00:00:00.000 abc -500.0000 -300.0000
4 2007-04-11 00:00:00.000 abc 100.0000 -200.0000
5 2007-04-11 00:00:00.000 abc合计 -100.0000 -200.0000
6 2007-04-11 00:00:00.000 def -200.0000 500.0000
7 2007-04-11 00:00:00.000 def 100.0000 600.0000
8 2007-04-11 00:00:00.000 def -400.0000 200.0000
9 2007-04-11 00:00:00.000 def合计 -500.0000 200.0000
id int , Date datetime , Name nvarchar(20) , [in] numeric(10,4) , money numeric(10,4))
insert into tableA
select 1, '2007-4-11' , 'abc' , 200 , 100
union select 2, '2007-4-11' , 'abc' , 100 , 200
union select 3, '2007-4-11' , 'abc' , -500 , -300
union select 4, '2007-4-11' , 'abc' , 100 , -200
union select 5, NULL , NULL , -100 , -200
union select 6, '2007-4-11' , 'def' , -200 , 500
union select 7, '2007-4-11' , 'def' , 100 , 600
union select 8, '2007-4-11' , 'def' , -400 , 200
union select 9, NULL , NULL , -500 , 200update a set Date = (select top 1 date from tableA as b where id < a.id order by b.id desc),
name = (select top 1 Name from tableA as c where id < a.id order by c.id desc)+'合计'
from tableA as a where a.name is null and a.date is null