SQL Server迭代求和

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