开发者

How do you find a missing number in a table field starting from a parameter and incrementing sequentially?

开发者 https://www.devze.com 2023-01-21 23:46 出处:网络
Let\'s say I have an sql server table: NumberTakenCompanyName 2                      Fred3 &n

Let's say I have an sql server table:

NumberTaken CompanyName

2                      Fred

3                      Fred

4                      Fred

6                      Fred

7                      Fred

8                      Fred

11        &nb开发者_Go百科sp;           Fred

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.

For example notice that 1, 5, 9 and 10 are missing from the table.

If I supplied the parameter [StartingNumber] = 1, it would check to see if 1 exists, if it does it would check to see if 2 exists and so on and so forth so 1 would be returned here.

If [StartNumber] = 6 the function would return 9.

In c# pseudo code it would basically be:

int ctr = [StartingNumber]
while([SELECT NumberTaken FROM tblNumbers Where NumberTaken = ctr] != null)    
    ctr++;

return ctr;

The problem with that code is that is seems really inefficient if there are thousands of numbers in the table. Also, I can write it in c# code or in a stored procedure whichever is more efficient.

Thanks for the help


Fine, if this question isn't going to be closed, I may as well Copy and paste my answer from the other one:

I called my table Blank, and used the following:

declare @StartOffset int = 2
; With Missing as (
    select @StartOffset as N where not exists(select * from Blank where ID = @StartOffset)
), Sequence as (
    select @StartOffset as N from Blank where ID = @StartOffset
    union all
    select b.ID from Blank b inner join Sequence s on b.ID = s.N + 1
)
select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))

You basically have two cases - either your starting value is missing (so the Missing CTE will contain one row), or it's present, so you count forwards using a recursive CTE (Sequence), and take the max from that and add 1

Tables:

create table Blank (
    ID int not null,
    Name varchar(20) not null
)
insert into Blank(ID,Name)
select 2 ,'Fred' union all
select 3 ,'Fred' union all
select 4 ,'Fred' union all
select 6  ,'Fred' union all
select 7 ,'Fred' union all
select 8 ,'Fred' union all
select 11 ,'Fred'
go


I would create a temp table containing all numbers from StartingNumber to EndNumber and LEFT JOIN to it to receive the list of rows not contained in the temp table.


If NumberTaken is indexed you could do it with a join on the same table:

select T.NumberTaken -1 as MISSING_NUMBER 
from myTable  T
left outer join myTable T1
on T.NumberTaken= T1.NumberTaken+1
where T1.NumberTaken is null and t.NumberTaken >= STARTING_NUMBER
order by T.NumberTaken

EDIT

Edited to get 1 too


1> select 1+ID as ID from #b as b 
   where not exists (select 1 from #b where ID = 1+b.ID)
2> go
 ID         
 -----------
           5
           9
          12

Take max(1+ID) and/or add your starting value to the where clause, depending on what you actually want.

0

精彩评论

暂无评论...
验证码 换一张
取 消