select top 1 num+1
from yourtable t1
where not exists
(select num from yourtable t2 where t2.num=t1.num+1)
order by num
from yourtable t1
where not exists
(select num from yourtable t2 where t2.num=t1.num+1)
order by num
用一个查询也可以完成,不过太麻烦.思路是使用自连接.
连接条件是该字段和该字段加一的值,在连接限制上采用left join
select @Num=max(num) from tablenamedeclare @i int
declare @Temp table (Num int)set @i=1while @i<=@Num+1 --- 如果全部连续,得到@Num+1
insert @Temp values(@i)select @Num=(
select top 1 num from @Temp a
where not exists (
select * from tablename where num=a.num
)
)
select @Num=max(num) from tablenamedeclare @i int
declare @Temp table (Num int)set @i=1while @i<=@Num+1 --- 如果全部连续,得到@Num+1
insert @Temp values(@i)select @Num=(
select top 1 num from @Temp a
where not exists (
select * from tablename where num=a.num
)
order by num
)
select @Num=max(num) from tablenamedeclare @i int
declare @Temp table (Num int)set @i=1while @i<=@Num+1 --- 如果全部连续,得到@Num+1
begin
insert @Temp values(@i)
set @i=@i+1
end
select @Num=(
select top 1 num from @Temp a
where not exists (
select * from tablename where num=a.num
)
order by num
)