找出最小缺失值的小技巧

新建一张表

 create table T1
(keycol int not null primary key check(keycol>0),
datacol varchar(10) not null
 )
insert into dbo.T1(keycol,datacol) values(3,'a');
 insert into dbo.T1(keycol,datacol) values(4,'a');
insert into dbo.T1(keycol,datacol) values(6,'a');
insert into dbo.T1(keycol,datacol) values(7,'a');

 

然后找出最小缺失值,如这时是返回1

然后再插入1,2键值两行,即返回是5

实现方法

select
case
 when not exists(select * from dbo.T1 where keycol=1) then 1
else
  (
   select min(keycol+1)
 from dbo.T1 as A
 where not exists (
   select * from dbo.T1 as B where B.keycol=A.keycol+1
  )
)
end