最大递归已用尽

问题描述

我需要一种有效的方法来传递参数[StartingNumber]并从[StartingNumber]开始依次计数，直到找到丢失的数字为止。

I need an efficient way to pass in a parameter [StartingNumber] and to count from [StartingNumber] sequentially until I find a number that is missing.

以下sql获取下一个数字：

I use the following sql to get the next number:

      DECLARE @StartOffset int
        SET  @StartOffset = 23
        ; With Missing as (
            select @StartOffset as N where not exists(
    select * from [QUEUE] where QueueNum = @StartOffset AND ismelutash = 1)
        ), Sequence as 
    (       select @StartOffset as N from [QUEUE] where QueueNum = @StartOffset 
            union all
            select b.QueueNum from [QUEUE] b inner join Sequence s 
on b.QueueNum = s.N + 1 and b.ismelutash = 1
        )
        select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))

它已经运行了一段时间，但是现在当我运行它时，我得到'该语句终止了。最高递归100已在语句完成前用尽。’

It has been working for awhile but now when I run it I get 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

有人有什么想法吗？谢谢

Anyone have any ideas? Thanks

编辑：

我添加了maxrecursion，但它只是加载而不会返回数据：

I added maxrecursion but it just loads and doesn't return data:

DECLARE @StartOffset int
SET  @StartOffset = 50

DECLARE @isMelutash int
SET @isMelutash = 0


; With QueueFilters as (
    select queuenum from queue where ismelutash = 1
),  Missing as (
    select @StartOffset as N where not exists(select * from QueueFilters where queuenum = @StartOffset)
), Sequence as (
    select @StartOffset as N from QueueFilters  where queuenum = @StartOffset
    union all
    select b.queuenum from QueueFilters  b inner join Sequence s on b.queuenum = s.N + 1
)
select COALESCE((select N from Missing ),(select MAX(N)+1 from Sequence ) )
**OPTION(MAXRECURSION 150)**

推荐答案

您可以使用您当前代码中的选项

You could use the MAXRECURSION option in your current code

但是，没有需要逐行递归（使用Itzik Ben-Gan的方法）。这将检测到队列中也没有ismelutash = 1的情况，因为它使用Tally表作为参考序列

However, there is no need to recurse line by line (using Itzik Ben-Gan's method). This will detect cases where there are no rows on Queue with ismelutash = 1 too because it uses the Tally table as a reference sequence

;WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)
 select TOP 1
    Number 
 from 
    Tally T
    LEFT JOIN   --corrected, oops.
    [QUEUE] Q ON T.Number = Q.QueueNum AND Q.ismelutash = 1
 where
    T.Number >= @StartOffset AND T.Number <= 1000000
    AND
    Q.QueueNum IS NULL
 ORDER BY
    T.Number

编辑：

原始代码，末尾带有MAXRECURSION提示 。 也更好

Original code with MAXRECURSION hint at the end. This link is better too

      DECLARE @StartOffset int
        SET  @StartOffset = 23
        ; With Missing as (
            select @StartOffset as N where not exists(
    select * from [QUEUE] where QueueNum = @StartOffset AND ismelutash = 1)
        ), Sequence as 
    (       select @StartOffset as N from [QUEUE] where QueueNum = @StartOffset 
            union all
            select b.QueueNum from [QUEUE] b inner join Sequence s 
on b.QueueNum = s.N + 1 and b.ismelutash = 1
        )
        select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))
        OPTION (MAXRECURSION 0)

这篇关于最大递归已用尽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！