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='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
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)