如何写select语句列出数据库中某ID范围段，如001-100之间缺乏的数字？

Question

select right(id+1001,3) 
from tabSample a 
where not exists(select 1 from tabSample where cast(ID as int)=cast(a.ID as int)+1)

Answer 1

--得出范围比较好
declare @tabSample table
 (
    id  char(3) 
)
insert @tabSample select N'001' 
insert @tabSample select N'002' 
insert @tabSample select N'004' 
insert @tabSample select N'005' 
insert @tabSample select N'006' 
insert @tabSample select N'008' 
insert @tabSample select N'012' select
a.ID as start,
min(b.ID) as 'end'
from 
(select 
ID
from 
@tabSample a 
where 
not exists(select 1 from @tabSample where cast(ID as int)=cast(a.ID as int)+1)
)a,
(select 
ID
from 
@tabSample a 
where 
not exists(select 1 from @tabSample where cast(ID as int)=cast(a.ID as int)-1)
)b
where
a.ID<b.ID
group by a.ID
（所影响的行数为 1 行）start end  
----- ---- 
002   004
006   008
008   012（所影响的行数为 3 行）

Answer 2

select right(id+1001,3) 
from tabSample a 
where not exists(select 1 from tabSample where cast(ID as int)=cast(a.ID as int)+1)
  and a.id<(select max(id) from tabSample)

Answer 3

参考
set nocount on
declare @t table(a int,b char(6),flag int)
insert @t select 1,'one',NULL
insert @t select 2,'two',NULL
insert @t select 3,'three',NULL
insert @t select 5,'four',NULL
insert @t select 6,'five',NULL
insert @t select 10,'six',NULL
insert @t select 11,'seven',NULLselect *
from @t aa
where  (a - (select max(a) from @t where aa.a > a)) > 1
/*a           b      flag        
----------- ------ ----------- 
5           four   NULL
10          six    NULL
*/

Answer 4

set nocount on
declare @t table(id  char(3))
insert @t select '001'
insert @t select '002'
insert @t select '004'
insert @t select '005'
insert @t select '009'
insert @t select '010'
insert @t select '011'select top 100 identity(int,1,1) as id  into # from sysobjects a,sysobjects bselect right('000'+ltrim(a.id),3) as col
from # a
where not exists(select 1 from @t where cast(id as int) = a.id)
and a.id <= (select cast(max(id) as int) from @t)drop table #/*col    
------ 
003
006
007
008
*/

Answer 5

select *
from tb aa
where  (a - (select max(id) from tb where aa.id > id)) > 1

Answer 6

create table tb(id  char(3))
insert into tb values('001') 
insert into tb values('002') 
insert into tb values('004') 
insert into tb values('005') 
insert into tb values('006') 
insert into tb values('008') 
insert into tb values('010')
goselect id = right('000'+cast(cast(id as int) - 1 as varchar),3) from tb aa where (cast(id as int) - (select cast(max(id) as int) from tb where aa.id > id)) > 1drop table tb/*
id     
------ 
003
007
009
（所影响的行数为 3 行）
*/

Answer 7

测试龟的，发现问题create table tb(id  char(3))
insert into tb values('001') 
insert into tb values('002') insert into tb values('006') 
insert into tb values('008') 
insert into tb values('010')
goselect id = right('000'+cast(cast(id as int) - 1 as varchar),3) from tb aa where (cast(id as int) - (select cast(max(id) as int) from tb where aa.id > id)) > 1drop table tb
/*id     
------ 
005
007
009（所影响的行数为 3 行）
*/

Answer 8

create table tb(id  char(3))
insert into tb values('001') 
insert into tb values('002') 
insert into tb values('004') 
insert into tb values('005') 
insert into tb values('006') 
insert into tb values('008') 
insert into tb values('010')
godeclare @id int,@maxid char(3)
select @maxid=max(id) from tbset @id=1
while not exists(select * from tb where convert(int,id)=@id) and @id<=@maxid
begin
select right(convert(char(4),1000+@id),3)
set @id=@id+1
endwhile exists(select * from tb a where convert(int,id)>=@id and not exists(select * from tb where convert(int,id)=a.id+1) and id<=@maxid) 
begin
set @id=(select top 1 convert(int,id)+1 from tb a where convert(int,id)>=@id and not exists(select * from tb where   convert(int,id)=a.id+1) and id<=@maxid order by id)
while not exists(select * from tb where convert(int,id)=@id) and @id<@maxid
begin
select right(convert(char(4),1000+@id),3)
set @id=@id+1
end
   
end

Answer 9

create table tabSample (id  char(3))
insert into tabSample  values('001') 
insert into tabSample  values('002') 
insert into tabSample  values('004') 
insert into tabSample  values('005') 
insert into tabSample  values('006') 
insert into tabSample  values('008') 
insert into tabSample  values('010')
goselect top 100 id=identity(int,1,1) into # from sysobjects
select right('000'+ltrim(id),3)[id] from # a where not exists(select 1 from tabsample where id=a.id)

Answer 10

create table tb(id  char(3))
insert into tb values('001') 
insert into tb values('002') 
insert into tb values('004') 
insert into tb values('005') 
insert into tb values('006') 
insert into tb values('008') 
insert into tb values('010')select right(id+999,3) id from tb a where not exists(Select 1 from tb where id=right(a.id+999,3)) and id>'001'
/*id     
------ 
003
007
009*/
drop table tb

Answer 11

参考：
create table tb(id  char(3))
insert into tb values('001') 
insert into tb values('002') 
insert into tb values('004') 
insert into tb values('005') 
insert into tb values('006') 
insert into tb values('008') 
insert into tb values('010')
insert into tb values('020')select (select right(max(id)+1001,3) from tb where id<a.id) bid ,right(id+999,3) eid from tb a where not exists(Select 1 from tb where id=right(a.id+999,3)) and id>'001'
/*id     
bid    eid    
------ ------ 
003    003
007    007
009    009
011    019
*/
drop table tb

Answer 12

再参考：
create table tb(id  char(3))
--insert into tb values('001') 
--insert into tb values('002') 
insert into tb values('004') 
insert into tb values('005') 
insert into tb values('006') 
insert into tb values('008') 
insert into tb values('010')
insert into tb values('020')select isnull((select right(max(id)+1001,3) from tb where id<a.id),'001') bid ,right(id+999,3) eid from tb a where not exists(Select 1 from tb where id=right(a.id+999,3)) and id>'001'/*    
bid    eid    
------ ------ 
001    003
007    007
009    009
011    019
*/
drop table tb

Answer 13

create table testTab(id char(3))
declare @int1 int
set @int1=1
while @int1<=10
begin
if @int1<>5
begin
insert into testTab values('00'+cast(@int1 as varchar(2)))
end
set @int1=@int1+1
end
select * from testTabdeclare  @rowcount int
declare @char char(3)
set @rowcount=@@rowcountwhile 1<=@rowcount
begin
set @char='00'+cast(@rowcount as varchar(3))
if not exists (select 1 from testTab where id=@char)
begin
print @char
end
set @rowcount=@rowcount-1
endoutput:005