SQL Server 2008 R2——用CTE进行递归计算求解累计值

请通过右侧公告中的“联系邮箱（wlsandwho@foxmail.com）”联系我

勿用于学术性引用。

勿用于商业出版、商业印刷、商业引用以及其他商业用途。　　　　　　　　　　　　　　　　

本文不定期修正完善。

本文链接：http://www.cnblogs.com/wlsandwho/p/4968075.html

耻辱墙：http://www.cnblogs.com/wlsandwho/p/4206472.html

=======================================================================

话不多说先上问题

鄙视垃圾爬虫网站祝你们早生极乐

=======================================================================

乍看群友的叙述，是要想很多事情的，大概两分钟（虚词）后，就可以分析出

余额'=余额+借方-贷方

然而关键的一点是，1+0=1，1-0=1。此为小学知识。意思是加减零对运算结果不影响。

所以问题中需要进行判断的地方只有“方向”列。而“方向”列的计算依据是余额，所以只要先专心致志的求解出“余额”列就行了。

（此处省略了群友自己给出的分析，没有为什么。经常在群里回答问题的人都知道这其中的厉害。）

=======================================================================

承蒙园友ahdung在上一篇博文(传送门)里不吝赐教，此处王林森尝试着使用CTE的递归进行问题求解。

=======================================================================

下面贴上王林森的代码（附带测试数据）

 1 -----------------------------------------------------------
 2 --网络代码有风险
 3 --复制粘贴须谨慎
 4 --wls 20151116
 5 USE tempdb
 6 GO
 7 
 8 IF OBJECT_ID (N't_DCRbyWLS', N'U') IS NOT NULL
 9 DROP TABLE t_DCRbyWLS;
10 GO
11 
12 CREATE TABLE t_DCRbyWLS(Debtor REAL,Creditor REAL,Direction NVARCHAR(1),Remainder REAL)
13 GO
14 
15 INSERT INTO t_DCRbyWLS(Direction,Remainder) VALUES ('借',84.9000)
16 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (3000.000,0.0000)
17 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,800.0000)
18 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,2284.9000)
19 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (1144.0000,0.0000)
20 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,1144.0000)
21 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (5000.0000,0.0000)
22 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,5000.0000)
23 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (436.0000,0.0000)
24 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,436.0000)
25 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,4000.0000)
26 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (5000.0000,0.0000)
27 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,960.0000)
28 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,800.0000)
29 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (800.0000,0.0000)
30 INSERT INTO t_DCRbyWLS(Debtor,Creditor) VALUES (0.0000,40.0000)
31 GO
32 
33 SELECT * FROM t_DCRbyWLS
34 GO
35 
36 WITH TempDCR
37 AS
38 (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS 'ID' ,Debtor,Creditor,Direction,Remainder FROM t_DCRbyWLS)
39 , 
40 TempReCursion
41 AS
42 (
43 SELECT TOP 1 ID, Debtor,Creditor,Remainder,Direction FROM TempDCR
44 UNION ALL
45 SELECT a.ID,a.Debtor,a.Creditor,b.Remainder+a.Debtor-a.Creditor,Direction=CASE WHEN b.Remainder+a.Debtor-a.Creditor>=0 THEN N'借' ELSE N'贷' END 
46     FROM TempDCR a JOIN TempReCursion b ON a.ID=b.ID+1
47 )
48 SELECT Debtor,Creditor,Direction,Remainder FROM TempReCursion

鄙视垃圾爬虫网站祝你们早生极乐

运行结果如下：

鄙视垃圾爬虫网站祝你们早生极乐

作为一只C++，我做SQL的宗旨是“不求高效，但求能跑”。

下面附上执行计划：

鄙视垃圾爬虫网站祝你们早生极乐

=======================================================================

（友情支持请扫描这个）

微信扫描上方二维码捐赠