给你一个效率不高的算法:(我没试)select a.借方, a.贷方, (select sum(b.借方-b.贷方) from table as b where b.key<=a.key) as 余额 from table as a order by key
用游标啦 我是将表导了出来又加多了两个字段 declare @借方 int declare @id int declare @贷方 int declare @余额 int declare @temp int declare p cursor for select * from table open p fetch next from p into @id,@借方,@贷方,@yu while @@fetch_status=0 begin if @id=1 begin select @余额=@借方 end else if @id>1 begin print str(@temp)+str(@借方)+str(@贷方) select @余额=@temp+@借方-@贷方 print str(@yu) end update temp1 set 余额=@余额 where tid=@id select @temp=@余额 fetch next from p into @id,@借方,@贷方,@余额 end close p deallocate p select * from table
例如:SELECT (SUM(借方)-SUM(贷方))AS BALANCE FORM MYTABLE
会出错的!我试了好象不能通过的。何况如果用
SELECT SUM(借方)AS XXX FROM TABLE
返回的只有1条记录,根本无法实现下列的情况。
借方 贷方 余额
----------------------------
80 80
34 46
12 58
13 71
21 50每一条记录都要有余额的。
a.贷方,
(select sum(b.借方-b.贷方) from table as b where b.key<=a.key) as 余额
from table as a
order by key
declare @借方 int
declare @id int
declare @贷方 int
declare @余额 int
declare @temp int
declare p cursor for select * from table
open p
fetch next from p into @id,@借方,@贷方,@yu
while @@fetch_status=0
begin
if @id=1
begin
select @余额=@借方
end
else if @id>1
begin
print str(@temp)+str(@借方)+str(@贷方)
select @余额=@temp+@借方-@贷方
print str(@yu)
end
update temp1 set 余额=@余额 where tid=@id
select @temp=@余额
fetch next from p into @id,@借方,@贷方,@余额
end
close p
deallocate p
select * from table