如何迅速找出某列里第一个不存在的整数值
假设有一列值类型为int,即值均为整数,有n行,n行里的数值并不是连续的,例:1,3,9,12,15,25......这样,我希望从1开始向大数遍历,找到最小的那个该列里不存在的值,如何实现?在数据库外部用逻辑编程的方法要频繁连接数据库,当表行数很多的时候,相当耗费时间.能否在数据库内部完成,最好是一条语句完成
[解决办法]
select MIN(rowid) minid
from (
select ROW_NUMBER() over(order by id) as rowid,id
from tb) as a
where rowid<>id
[解决办法]
2005:
create table tb(num1 int)
insert into tb select 3 union all select 6 union all select 9
go
select min(number) from master..spt_values a
where type='p' and number>0 and not exists(select 1 from tb where num1=a.number)
/*
-----------
1
(1 行受影响)
*/
go
insert into tb select 1
select min(number) from master..spt_values a
where type='p' and number>0 and not exists(select 1 from tb where num1=a.number)
/*
-----------
2
(1 行受影响)
*/
go
