用下面的方法就行了.--生成数据处理临时表
select id=identity(int,0,1),* into #tb
from(
select acno,date,amnt,ye=cast(0 as decimal(20,2))
from vouch a
where lsh=(select max(lsh) from vouch where date=a.date and acno=a.acno)
union all
select acno,convert(char(8),convert(datetime,max(date),112)+1,112),0,0 from vouch group by acno
) a order by acno,date,amnt desc--处理数据
declare @acno char(16),@date char(8),@ye decimal(20,2),@yesum decimal(20,2)update #tb set @yesum=case acno when @acno then @yesum+@ye*(datediff(day,@date,date)) else 0 end
,@ye=amnt,@acno=acno,@date=date
,ye=@yesum
from #tb--显示结果
select acno,余额=ye from #tb a where id=(select max(id) from #tb where acno=a.acno)
select id=identity(int,0,1),* into #tb
from(
select acno,date,amnt,ye=cast(0 as decimal(20,2))
from vouch a
where lsh=(select max(lsh) from vouch where date=a.date and acno=a.acno)
union all
select acno,convert(char(8),convert(datetime,max(date),112)+1,112),0,0 from vouch group by acno
) a order by acno,date,amnt desc--处理数据
declare @acno char(16),@date char(8),@ye decimal(20,2),@yesum decimal(20,2)update #tb set @yesum=case acno when @acno then @yesum+@ye*(datediff(day,@date,date)) else 0 end
,@ye=amnt,@acno=acno,@date=date
,ye=@yesum
from #tb--显示结果
select acno,余额=ye from #tb a where id=(select max(id) from #tb where acno=a.acno)
declare @vouch table(date char(8),lsh char(6),acno char(16),amnt decimal(20,2))
insert into @vouch
select '20030514','160001','9011500201000088',10000.00
union all select '20030514','160002','9011500201000088',200.00
union all select '20030618','150001','9011500201000088', 1500.00
union all select '20030711','120005','9011500201000088',20000.00
union all select '20030711','120007','9011500201000088',35000.00
union all select '20030811','120007','9011500201000089',35000.00--生成数据处理临时表
select id=identity(int,0,1),* into #tb
from(
select acno,date,amnt,ye=cast(0 as decimal(20,2))
from @vouch a
where lsh=(select max(lsh) from @vouch where date=a.date and acno=a.acno)
union all
select acno,convert(char(8),convert(datetime,max(date),112)+1,112),0,0 from @vouch group by acno
) a order by acno,date,amnt desc--处理数据
declare @acno char(16),@date char(8),@ye decimal(20,2),@yesum decimal(20,2)update #tb set @yesum=case acno when @acno then @yesum+@ye*(datediff(day,@date,date)) else 0 end
,@ye=amnt,@acno=acno,@date=date
,ye=@yesum
from #tb--显示结果
select acno,余额=ye from #tb a where id=(select max(id) from #tb where acno=a.acno)--删除数据处理临时表
drop table #tb/*--下面是在我的电脑上的测试结果--*/
acno 余额
---------------- ----------------------
9011500201000089 35000.00
9011500201000088 76500.00（所影响的行数为 2 行）
--*/