sql server SELECT 递归检索的应用

迭代检索

原理就是WITH语句做一个临时表【DAYS】，在UNION ALL的下半部分
从【DAYS】临时表中检索YMD的前一天，当【DAYS】表中的YMD大于等于当前日期的前10天
迭代停止

WITH DAYS AS 
 (SELECT CURRENT_TIMESTAMP AS YMD
 UNION ALL
 SELECT DATEADD(Y, -1, YMD)
 FROM DAYS
 WHERE DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
 )
 SELECT * FROM DAYS OPTION ( MAXRECURSION 32767 )
红色字体部分，控制迭代的次数， 32767 为最大次数。

结果：
YMD
2015-10-20 15:32:15.713
2015-10-19 15:32:15.713
2015-10-18 15:32:15.713
2015-10-17 15:32:15.713
2015-10-16 15:32:15.713
2015-10-15 15:32:15.713
2015-10-14 15:32:15.713
2015-10-13 15:32:15.713
2015-10-12 15:32:15.713
2015-10-11 15:32:15.713
2015-10-10 15:32:15.713
2015-10-09 15:32:15.713

-----------------------------------------------------

另一种迭代检索
较为复杂的应用
【Statics】表
Code    PortID    Div    Quantity    TotalPosition    No
---------------------------------------------------------
1301    A001      04     50          2000             1
1301    A001      04     100         2000             2
1301    A001      04     150         2000             3
1301    A001      04     200         2000             4
1301    A001      04     250         2000             5
1301    A001      04     300         2000             6
1301    A001      04     350         2000             7
1301    A001      04     400         2000             8
1301    A001      04     450         2000             9
1301    A001      04     500         2000             10

现在要对【Statics】表的Quantity和TotalPosition进行如下规则的更新

左连接条件
当前记录.No = 前一条记录.No + 1

更新内容
当前记录.Quantity = IIF(ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition) - 当前记录.Quantity > 0,
                        ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition) - 当前记录.Quantity, 0),
当前记录.TotalPosition = IIF(当前记录.Quantity - ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition) > 0,
                             当前记录.Quantity - ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition), 0)

期待的结果是
【Statics】表
Code    PortID    Div    Quantity    TotalPosition    No
---------------------------------------------------------
1301    A001      04     0           1950             1
1301    A001      04     0           1850             2
1301    A001      04     0           1700             3
1301    A001      04     0           1500             4
1301    A001      04     0           1250             5
1301    A001      04     0           950              6
1301    A001      04     0           600              7
1301    A001      04     0           200              8
1301    A001      04     0           0                 9
1301    A001      04     0           0                10

==========实装方案1=======================================
如果直接实装成下面的样子，则无法达到想要的结果

UPDATE
  当前记录
SET
  当前记录.Quantity = IIF(ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition) - 当前记录.Quantity > 0,
                          ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition) - 当前记录.Quantity, 0),
  当前记录.TotalPosition = IIF(当前记录.Quantity - ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition) > 0,
                               当前记录.Quantity - ISNULL(前一条记录.TotalPosition, 当前记录.TotalPosition), 0)
FROM
  Statics 当前记录
  LEFT OUTER JOIN Statics 前一条记录
  ON 当前记录.No = 前一条记录.No + 1

具体的原因是，左外结合的时间点，Statics表的数据就会被做成一个静态的内存表
在参照的时候，前一条记录被更新掉的Quantity和TotalPosition
对于当前记录来说， 始终都是不可见的。

==========实装方案2=======================================
一个SQL语句直接更新不可能的话，就换思路，考虑是否可以将想要的结果
直接检索出来，所以要用到WITH创建一个临时表【集計】

WITH 集計 AS
(
---------------------------Area1--------------------------
SELECT
  IIF(No=1,1,0) AS FLG,
  Code,
  PortID,
  Div,
  IIF(No=1,CAST(0 AS Numeric(23,5)),Quantity) AS Quantity,
  IIF(No=1,TotalPosition-Quantity,TotalPosition) AS TotalPosition,
  No
FROM Statics
---------------------------Area1--------------------------
UNION ALL 
---------------------------Area2--------------------------
SELECT
  1 AS FLG,
  initial.Code,
  initial.PortID,
  initial.Div,
  IIF(initial.Quantity - all.TotalPosition >0, initial.Quantity - all.TotalPosition, CAST(0 AS Numeric(23,5))) AS TotalPosition,
  IIF(all.TotalPosition - initial.Quantity>0, all.TotalPosition - initial.Quantity, 0) AS TotalPosition,
  initial.No
FROM 集計 all JOIN Statics initial ON initial.No = (all.No + 1)
WHERE all.FLG=1
---------------------------Area2--------------------------
)
SELECT * FROM 集計
WHERE FLG=1
ORDER BY No

为了方便理解
把UnionAll的上下两部分 ，拆成 Area1 和Area2

演示一下，Area1和Area2的检索， 对临时表【集計】的影响

Area1 第1次
【集計】表 --------------------------<<<<<<<<< FLG  Code    PortID    Div    Quantity    TotalPosition    No
--------------------------------------------------------------
1    1301    A001      04     0           1950             1
0    1301    A001      04     100         2000             2
0    1301    A001      04     150         2000             3
0    1301    A001      04     200         2000             4
0    1301    A001      04     250         2000             5
0    1301    A001      04     300         2000             6
0    1301    A001      04     350         2000             7
0    1301    A001      04     400         2000             8
0    1301    A001      04     450         2000             9
0    1301    A001      04     500         2000             10

Area2 第1次
【集計】表 --------------------------<<<<<<<<< FLG  Code    PortID    Div    Quantity    TotalPosition    No
--------------------------------------------------------------
1    1301    A001      04     0           1950             1
0    1301    A001      04     100         2000             2
1    1301    A001      04     0           1850             2
0    1301    A001      04     150         2000             3
0    1301    A001      04     200         2000             4
0    1301    A001      04     250         2000             5
0    1301    A001      04     300         2000             6
0    1301    A001      04     350         2000             7
0    1301    A001      04     400         2000             8
0    1301    A001      04     450         2000             9
0    1301    A001      04     500         2000             10

Area2 第2次
【集計】表 --------------------------<<<<<<<<< FLG  Code    PortID    Div    Quantity    TotalPosition    No
--------------------------------------------------------------
1    1301    A001      04     0           1950             1
0    1301    A001      04     100         2000             2
1    1301    A001      04     0           1850             2
0    1301    A001      04     150         2000             3
1    1301    A001      04     150         1700             3
0    1301    A001      04     200         2000             4
0    1301    A001      04     250         2000             5
0    1301    A001      04     300         2000             6
0    1301    A001      04     350         2000             7
0    1301    A001      04     400         2000             8
0    1301    A001      04     450         2000             9
0    1301    A001      04     500         2000             10

Area2 第3次
【集計】表 --------------------------<<<<<<<<< FLG  Code    PortID    Div    Quantity    TotalPosition    No
--------------------------------------------------------------
1    1301    A001      04     0           1950             1
0    1301    A001      04     100         2000             2
1    1301    A001      04     0           1850             2
0    1301    A001      04     150         2000             3
1    1301    A001      04     150         1700             3
0    1301    A001      04     200         2000             4
1    1301    A001      04     200         1500             4
0    1301    A001      04     250         2000             5
0    1301    A001      04     300         2000             6
0    1301    A001      04     350         2000             7
0    1301    A001      04     400         2000             8
0    1301    A001      04     450         2000             9
0    1301    A001      04     500         2000             10

......

假设表的记录数 = No = N

Area2 第N-1 = 9次
【集計】表 --------------------------<<<<<<<<< FLG  Code    PortID    Div    Quantity    TotalPosition    No
--------------------------------------------------------------
1    1301    A001      04     0           1950             1
0    1301    A001      04     100         2000             2
1    1301    A001      04     0           1850             2
0    1301    A001      04     150         2000             3
1    1301    A001      04     150         1700             3
0    1301    A001      04     200         2000             4
1    1301    A001      04     200         1500             4
0    1301    A001      04     250         2000             5
1    1301    A001      04     250         1250             5
0    1301    A001      04     300         2000             6
1    1301    A001      04     300         950              6
0    1301    A001      04     350         2000             7
1    1301    A001      04     350         600              7
0    1301    A001      04     400         2000             8
1    1301    A001      04     400         200              8
0    1301    A001      04     450         2000             9
1    1301    A001      04     450         0                9
0    1301    A001      04     500         2000             10
1    1301    A001      04     500         0                10

则整个检索，一共会出现 N + (N -1) = 10 + (10 -1) = 19 条记录

关于Area2的检索条件 集計.FLG = 1

仅有这一个条件的话，伴随着检索次数的增加，会有复数条记录符合条件
(因为FLG = 1的记录会越来越多)
随之似乎也应该有很多的记录被生成出来

但似乎，似乎SQL SERVER内部对此有着自己的控制
以下纯属臆测，不可信度很高
【集計】中的所有记录，包括Area2追加进去的记录
对于Area2的每次检索，都是可见的。
但Area2只会处理刚刚由自己追加到【集計】里的那条记录
所以 才会一共只加入到【集計】里9条记录

至于为何每次Area2只能看到自己刚刚加进去的那条记录
可能是因为第一次Area2检索的时候，符合条件的数据只有1件
或者
No = No +1 在起作用吧