SQL Server迭代求和

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89

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='geovindu' 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.invoiceno as '銷售單號碼', a.indate as '銷售單日期',a.amount as '所得/已使用積分',  (select sum(amount) from [dbo].[GetVipNoDlyList] ('geovindu') b where b.VID <= a.VID) as '可用積分',a.dcr as '積分類別'  from [dbo].[GetVipNoDlyList] ('geovindu') a