求一个逐行计算更新语句

[大地主刘发财]

表结构如下
CID 年 月 期初借 期初贷 借 贷 期末借 期末贷 是否期初行
3554 2017 8 0 5000 0 5000 0 5000 0
3554 2017 10 0 0 33000 8900 0 0 1
3554 2017 11 0 0 20790 20000 0 0 1
3554 2017 12 0 0 2160 10000 0 0 1
3558 2017 8 0 3000 0 3000 0 3000 0
3558 2017 10 0 0 33000 8900 0 0 1

版本使用的是SQL SERVER 2008R2
上表中，以CID和年月排序
是否期初行值为0时作为首行
上一行的期末数作为下一行的期初数，本行期初借+本行借=本行期末借，本行期初贷+本行贷=本行期末贷

例如上表中，CID为3554且是否期初行为0的记录作为首行，将期末借和期末贷写入CID为3554年=2017月=10这一行的期初借和期初贷中，然后计算期末借和期末贷，完成后再进行下一行的计算。
CID为3558的也是一样计算。
CID不固定，有很多。
如何能用一条语句更新上表？
结果如下图所示

[砸死牛顿的苹果]

就是累加,自我连接.

[听雨停了]

if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([CID] VARCHAR(20),[年] VARCHAR(20),[月] int,[期初借] int,[期初贷] int,[借] int,[贷] int,[期末借] int,[期末贷] int,[是否期初行] int)
Insert #tab
select '3554','2017',8,0,5000,0,5000,0,5000,0 union all
select '3554','2017',10,0,0,33000,8900,0,0,1 union all
select '3554','2017',11,0,0,20790,20000,0,0,1 union all
select '3554','2017',12,0,0,2160,10000,0,0,1 union all
select '3558','2017',8,0,3000,0,3000,0,3000,0 union all
select '3558','2017',10,0,0,33000,8900,0,0,1
--测试数据结束

WITH cte AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY cid ORDER BY 是否期初行,年,月) as rn FROM #tab 	
),
cte2 AS (
	Select * from cte WHERE rn=1
	UNION ALL
	SELECT a.cid,a.年,a.月,b.期末借,b.期末贷,a.借,a.贷,b.期末借+a.借,b.期末贷+a.贷,a.是否期初行,a.rn FROM cte a 
	INNER JOIN cte2 b ON a.cid=b.cid AND a.rn=b.rn+1 
)
SELECT cid,年,月,期初借,期初贷,借,贷,期末借,期末贷,是否期初行 FROM cte2
ORDER BY cid,年,月

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([CID] NVARCHAR(100),[年] NVARCHAR(100),[月] int,[期初借] int,[期初贷] int,[借] int,[贷] int,[期末借] int,[期末贷] int,[是否期初行] int)
Insert #T
select '3554','2017',8,0,5000,0,5000,0,5000,0 union all
select '3554','2017',10,0,0,33000,8900,0,0,1 union all
select '3554','2017',11,0,0,20790,20000,0,0,1 union all
select '3554','2017',12,0,0,2160,10000,0,0,1 union all
select '3558','2017',8,0,3000,0,3000,0,3000,0 union all
select '3558','2017',10,0,0,33000,8900,0,0,1
Go
--测试数据结束
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY CID ORDER BY 年,月) AS Num FROM #T
),cteb AS (
SELECT  ctea.CID ,
        ctea.年 ,
        ctea.月 ,
        ctea.期初借 ,
        ctea.期初贷 ,
        ctea.借 ,
        ctea.贷 ,
        期初借+借 AS 期末借,
        期初贷+贷 AS 期末贷,
        ctea.是否期初行 ,
        ctea.Num
FROM    ctea
WHERE   Num = 1
UNION ALL
SELECT  ctea.CID ,
        ctea.年 ,
        ctea.月 ,
        cteb.期末借 ,
        cteb.期末贷 ,
        ctea.借 ,
        ctea.贷 ,
        cteb.期末借+ctea.借 AS 期末借,
        cteb.期末贷+ctea.贷 AS 期末贷,
        ctea.是否期初行 ,
        ctea.Num
FROM    ctea
        JOIN cteb ON cteb.Num  + 1= ctea.Num AND cteb.CID = ctea.CID
)
SELECT  CID ,
        年 ,
        月 ,
        期初借 ,
        期初贷 ,
        借 ,
        贷 ,
        期末借 ,
        期末贷 ,
        是否期初行
FROM    cteb
ORDER BY CID ,
        年 ,
        月

[听雨停了]

引用 3 楼 sinat_28984567 的回复:

很羡慕你们用客户端回答竟然不乱码，还是这么整齐的sql

[二月十六]

引用 4 楼 qq_37170555 的回复:

[quote=引用 3 楼 sinat_28984567 的回复:]

很羡慕你们用客户端回答竟然不乱码，还是这么整齐的sql[/quote] 为了多得点分，在电脑上写好，客户端回复的