MSSQL 中循环迭代 应用 求累计数 实例
If object_id(N'tempdb..#Ta',N'U') is not null
DROP Table #Ta
If object_id(N'tempdb..#Tb',N'U') is not null
DROP Table #Tb
select * into #ta from
(Select top 100 PERCENT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS ID,Skrq,xh,CustomerId,je,0 Remainder ,bz
FROM money where convert(nvarchar(18),CustomerID)= :CustomerID order by Skrq, xh) C order by Skrq, xh
--select * from #ta where convert(nvarchar(18),CustomerID)= '0604-60046841'
-- 得到基础表，关键得有一个唯一字段 ID
select * into #tb from
(Select a.ID, Skrq,
left(convert(char(10),a.skrq,120),7) kjqj,a.xh,a.CustomerId,
case when je>0 then round(je,2) else 0 end skje, --收款金额
case when je<0 then -round(je,2) else 0 end zcje, --支出金额
(select round(sum(je),2) from #ta b where b.id<=a.id and skrq<=a.skrq) as Remainder ,bz
FROM #ta a) D --得到每天余额
select zbh,id,kjqj,skrq,bz,
( case skje when 0 then null else skje end) skje,
( case zcje when 0 then null else zcje end) zcje,
( case ye when 0 then null else ye end) ye
from
(
select '0' zbh,E.ID,E.kjqj,E.Skrq,E.CustomerId, skje, zcje, E.Remainder ye ,E.bz From #tb E --读取明细
union all
select '1' zbh,null ID,G.kjqj,null Skrq,null CustomerId, sum(G.skje) skje, sum(G.zcje) zcje,
(select top 1 Remainder from --关键要得到：月末的余额
(select top 100 PERCENT ID,kjqj,Remainder From #tb ) F
where F.kjqj=G.kjqj order by ID Desc) ye, '本月小计' bz
From #tb G group by G.kjqj --按会计期间汇总
) H order by 3,1,2