使用日期提取事务的递归

Question

我是SQL的新手，在递归日期方面遇到了问题。我基本上是在试图找出我们的库存在特定时间点的位置，并在之后的几天内递归地这样做（取决于时间跨度）。

事务_CTE示例：

+------------------+-----------+--------------------+-----------------+-------+
| intTransactionID | intInvBID | varTransactionType |   dteDateTime   | intTo |
+------------------+-----------+--------------------+-----------------+-------+
|                1 | Item1     | I                  | 8/21/2014 11:03 |  1111 |
|                2 | Item1     | I                  | 8/23/2014 17:20 |  2222 |
|                3 | Item2     | I                  | 8/21/2014 11:03 |  1111 |
+------------------+-----------+--------------------+-----------------+-------+

通缉结果：

+---------------+-----------+------------+-----------------+------------+
| dteTargetDate | intInvBID | varBarcode |   dteDateTime   | LocationID |
+---------------+-----------+------------+-----------------+------------+
| 8/22/2014     | Item1     |       8888 | 8/21/2014 11:03 |       1111 |
| 8/22/2014     | Item2     |       9999 | 8/21/2014 11:03 |       1111 |
| 8/23/2015     | Item1     |       8888 | 8/23/2014 17:20 |       2222 |
| 8/23/2015     | Item2     |       9999 | 8/21/2014 11:03 |       1111 |
+---------------+-----------+------------+-----------------+------------+

当我增加范围/更改endDate时，我最终会遇到以下错误：语句终止。在语句完成之前，最大递归100已经用完。

我认为我没有正确地执行递归，因为@startDate没有更改。

代码段：

    DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = '2014-08-22', @endDate = '2014-08-23'
;WITH BarcodeDetails_CTE (intInvBID, intInventoryID, varBarcode, varPartNo, varDescription)
    AS
    (
        -- All Barcodes with details
        SELECT 
            b.intInvBID,
            i.intInventoryID,
            b.varBarcode,
            i.varPartNo,
            i.varDescription
        FROM dbo.STG_Barcodes as b
        LEFT JOIN dbo.STG_Inventory as i
        ON b.intInventoryID = i.intInventoryID
        -- Optional Restriction
        WHERE i.varPartNo = 'A-01-040'
    ),
    Transactions_CTE (intTransactionID, intInvBID, varTransactionType, dteDateTime, intTo)
    AS
    (
        SELECT t.intTransactionID, t.intInvBID, t.varTransactionType, t.dteDateTime, t.intTo
        FROM dbo.STG_Transactions as t
        WHERE t.varTransactionType = 'I'
    ),
    Scans_CTE (intInvBID, varBarcode, dteTargetDate, dteDateTime, LocationID)
    AS
    (
        -- Anchor
        SELECT      bd.intInvBID,
                    bd.varBarcode,
                    @startDate as dteTargetDate,
                    t.dteDateTime,
                    t.intTo as LocationID
        FROM dbo.STG_Transactions as t
        INNER JOIN Transactions_CTE as tc
        ON t.intTransactionID = tc.intTransactionID AND t.dteDateTime <= @startDate 
        INNER JOIN BarcodeDetails_CTE as bd
        ON bd.intInvBID = t.intInvBID
        UNION ALL
        -- Recursive Member Definition
        SELECT      ls.intInvBID,
                    ls.varBarcode,
                    DATEADD(DAY,1,@startDate) as dteTargetDate,
                    ls.dteDateTime,
                    ls.LocationID
        FROM Scans_CTE as ls
        WHERE ls.dteTargetDate + 1 <= @endDate
    )
SELECT  rt.dteTargetDate,
        rt.intInvBID,
        rt.varBarcode,
        rt.dteDateTime,
        t.intTo as LocationID
FROM
    (SELECT ls.intInvBID,
            ls.varBarcode,
            ls.dteTargetDate,
            MAX(ls.dteDateTime) as dteDateTime
    FROM Scans_CTE as ls
    GROUP BY ls.dteTargetDate, ls.intInvBID, ls.varBarcode
    ) as rt
    INNER JOIN Transactions_CTE as t
    ON t.intInvBID = rt.intInvBID AND rt.dteDateTime = t.dteDateTime
ORDER BY dteTargetDate ASC;

Answer 1

默认递归值为100。您可以在递归cte:中使用特殊的表提示

; WITH    cte
          AS ( SELECT   1 AS A
               UNION ALL
               SELECT   A + 1 AS A
               FROM     cte
               WHERE    A < 32767
             )
    SELECT  *
    FROM    cte  OPTION  ( MAXRECURSION 32767 );

MAXRECURSION number指定允许的最大递归次数用于此查询。数字是介于0和32767之间的非负整数。如果指定了0，则不应用任何限制。如果没有此选项指定时，服务器的默认限制为100。当指定或在查询过程中达到MAXRECURSION限制的默认数字执行时，查询结束并返回错误。因为如果出现此错误，则该语句的所有效果都将回滚。如果语句是SELECT语句，可以是部分结果或无结果返回。返回的任何部分结果可能不包括上的所有行超过指定的最大递归级别的递归级别。对于有关详细信息，请参阅WITH common_table_expression（Transact-SQL）。

https://msdn.microsoft.com/en-us/library/ms181714.aspx