1.建立表 a 保存所有连续的编号
select a.id
from a
where not exist ( select 1 from b where a.id = b.id )
and a.id <= ( select max(id) from b )
select a.id
from a
where not exist ( select 1 from b where a.id = b.id )
and a.id <= ( select max(id) from b )
insert into @tb select 1
insert into @tb select 3
insert into @tb select 7
insert into @tb select 9select a.id from # a left join @tb b
on a.id=b.id
where b.id is null2
4
5
6
8
10
insert into @tb select 1
insert into @tb select 3
insert into @tb select 7
insert into @tb select 9select id from #
where id not in
(select id from @tb)
insert into @tb select 1
insert into @tb select 3
insert into @tb select 7
insert into @tb select 9select id from #
where id not in
(select id from @tb)就这意思
if exists
(
select * from jobs a
where job_id>1 and not exists
(
select * from jobs
where job_id=a.job_id-1
)
)
print '存在缺号'
else
print '不存在缺号'
SELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b select a.id from # a where id (not in select id from tb)
我的表格是 myuser 主键是 ID
,id-1 as 不存在的结束编号 from myuser a
where not exists(select 1 from myuser where id = a.id -1)