create table Mytable ( id int, Re varchar(50) )insert into Mytable values (1,newid()) insert into Mytable values (3,newid()) insert into Mytable values (4,newid()) insert into Mytable values (5,newid()) insert into Mytable values (7,newid())select min(id)+1 as 第一个不连续的ID from mytable where id not in (select A.id from Mytable A inner join Mytable B on A.id=B.id-1)第一个不连续的ID 2
select MIN(rowid) minid from ( select ROW_NUMBER() over(order by id) as rowid,id from tb) as a where rowid<>id
不是回答过了么: select top 1 rn from(select row_number()over(order by num1)rn from tb)a where not exists(select 1 from tb where num1=a.rn) OR 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)
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
create table #A(id int) insert into #A select 1 union select 2 union select 4 union select 10 --测试 select MIN(rowid) minid from ( select ROW_NUMBER() over(order by id) as rowid,id from #A) as a where rowid<>id --不清楚 你怎么测试的 ,怎么就 返回2???
(
id int,
Re varchar(50)
)insert into Mytable values (1,newid())
insert into Mytable values (3,newid())
insert into Mytable values (4,newid())
insert into Mytable values (5,newid())
insert into Mytable values (7,newid())select min(id)+1 as 第一个不连续的ID from mytable
where id not in
(select A.id from Mytable A inner join Mytable B
on A.id=B.id-1)第一个不连续的ID
2
from (
select ROW_NUMBER() over(order by id) as rowid,id
from tb) as a
where rowid<>id
select top 1 rn from(select row_number()over(order by num1)rn from tb)a
where not exists(select 1 from tb where num1=a.rn)
OR
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)
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
insert into #A
select 1 union
select 2 union
select 4 union
select 10
--测试
select MIN(rowid) minid
from (
select ROW_NUMBER() over(order by id) as rowid,id
from #A) as a
where rowid<>id
--不清楚 你怎么测试的 ,怎么就 返回2???