SQL Server迭代求和

[sql] 
drop table t_geovindu   
    
create table t_geovindu   
(   
    xid int IDENTITY (1, 1),   
    price money,   
    DebitCredit VARCHAR(2),   
    adate datetime default(getdate())   
        
)   
    
insert into t_geovindu(DebitCredit,price) values('C',10)   
insert into t_geovindu(DebitCredit,price) values('C',25)   
insert into t_geovindu(DebitCredit,price) values('C',36)   
insert into t_geovindu(DebitCredit,price) values('C',66)   
insert into t_geovindu(DebitCredit,price) values('D',-11)   
insert into t_geovindu(DebitCredit,price) values('C',32)   
insert into t_geovindu(DebitCredit,price) values('D',-50)   
    
    
--   
select a.xid, a.price,   
 (select sum(price) from t_geovindu b where b.xid <= a.xid) as Balance,DebitCredit     
from t_geovindu a   
    
--   
select xid, price,    
 (case  when Balance  is null then price else Balance  end ) as Balance    
from  
 (select a.xid, (select  sum(price) from t_geovindu b where b.xid < a.xid)  as Balance  , a.price   
from t_geovindu a)  x   
--   
    
select  sum(price) from t_geovindu b where (b.xid < a.xid)   
    
select a.xid, (select  sum(price) from t_geovindu b where b.xid < a.xid)  as Balance  , a.price   
from t_geovindu a   
    
---   
create function mysum(@xh int, @price int) returns int  
begin  
   return (select   
           (case when Balance  is null then @price  else Balance  end) as Balance     
          from ( select  sum(price) as Balance  from t_geovindu where xid < @xh) x)   
end  
---   
select xid, price, dbo.mysum(xid, price)  as Balance    
from t_geovindu   

create table vipnoDly   
(   
    VID Int IDENTITY (1, 1) PRIMARY KEY, invoiceno nvarchar(50),indate datetime, vipno nvarchar(50),amount int,dcr nvarchar(20)   
)   
go   
    
SET IDENTITY_INSERT [dbo].vipnoDly ON   
Insert vipnoDly(invoiceno,indate,vipno,amount,dcr) Select invoiceno,indate,vipno,amount,dcr From vipdly AS A Where vipno='654321' order by A.indate   
    
select * from vipnoDly   
    
--SET IDENTITY_INSERT dbo.Tool ON   
    
    
Create Function [dbo].[GetVipNoDlyList]   
(   
    @ID nvarchar(20)   
)   
Returns @Tree Table (VID Int IDENTITY (1, 1), invoiceno nvarchar(50),indate datetime, vipno nvarchar(50),amount int,dcr nvarchar(20))   
As  
Begin  
Insert @Tree(invoiceno,indate,vipno,amount,dcr) Select invoiceno,indate,vipno,amount,dcr From vipdly AS A Where vipno=@ID order by A.indate   
Return  
End  
GO   
    
select * from [dbo].[GetVipNoDlyList] ('geovindu') as a order by indate   
    
---SQL Server聚合函数和子查询迭代求和   
---如果ID不是第一   ， 出 第一行  合 有  ，所以需查 生成一 新的ID增      
select a.VID, a.amount,   
 (select sum(amount) from [dbo].[GetVipNoDlyList] ('geovindu') b where b.VID <= a.VID) as Balance     
from [dbo].[GetVipNoDlyList] ('geovindu') a