求一个查询语句

[cheyaoming]

数据表：ta
序号 姓名
001 张三
002 李四
003 XXX
。 。
。 。
100 XXX
101 XXX
。 。
。 。
中间有可能缺少了某个序号造成序号不连续。例如中间少了一个‘021 ’
如何查询出某两个序号之间（比如001--100）缺少的那个序号。（就是查询出‘021’这个数据表里本来不存在的序号）

[daishaodong]

master..spt_values where type='p'
有个问题，最大只有255，序号大于255就不行了。。。

[飘零一叶]

drop table ta
CREATE TABLE TA
(
  序号 varchar(10),
  姓名 nvarchar(20)
)
insert into TA
select '001','张三' union all
select '002','李四' union all
select '003','XXX' union all
select '005','XXX' union all
select '008','XXX' union all
select '010','XXX' 

--查询全部断号
select a.n from 
(select right('000'+ltrim(number),3) as n  from master..spt_values where type='p' and number between (select min(序号) from ta) and (select MAX(序号) from ta)) a left join TA b on a.n=b.序号 
where  b.序号 is null
/*
-----------
004
006
007
009
*/

--最小断号
select min(a.n) from 
(select right('000'+ltrim(number),3) as n  from master..spt_values where type='p' and number between (select min(序号) from ta) and (select MAX(序号) from ta)) a left join TA b on a.n=b.序号 
where  b.序号 is null
/*
----------
(无列名)
004
*/

[youngzi]

建一个临时表 @tmp，放进去所有的连续的序号，然后用
select * from @tmp where id not in (select id from table)

[mugua604]

create table ##tmp(id varchar(10),nam varchar(30))


insert into ##tmp(id,nam)
select '001','a'
union all
select '002','d'
union all
select '004','b'

select right(1001+number,3) from master.dbo.spt_values  a
where not exists(select 1 from ##tmp
					where  right(1001+number,3)=id)
AND type='P' AND  number<10

[mugua604]

create table ##tmp(id varchar(10),nam varchar(30))


insert into ##tmp(id,nam)
select '001','a'
union all
select '002','d'
union all
select '004','b'

select right(1001+number,3) from master.dbo.spt_values a
where not exists(select 1 from ##tmp
where right(1001+number,3)=id)
AND type='P' AND number<10

/*

------
003
005
006
007
008
009
010

(7 行受影响)


*/

[mugua604]

create table ##tmp(id varchar(10),nam varchar(30))


insert into ##tmp(id,nam)
select '001','a'
union all
select '002','d'
union all
select '004','b'

select right(1001+number,3) from master.dbo.spt_values a
where not exists(select 1 from ##tmp
  where right(1001+number,3)=id)
AND type='P' AND number<10

/*

------
003
005
006
007
008
009
010

(7 行受影响)


*/

[insus]

参考：
http://www.cnblogs.com/insus/articles/1934194.html

[华夏小卒]

select top 10000 id=identity(int,1,1) into #tb from sys.objects a,sys.objects b --top 后面的数字自己定

select right('000'+ltrim(id),3) from #tb where right('000'+ltrim(id),3) not in (select 序号 from ta)

[abuying]

declare @s varchar(1000)
select @s = coalesce(@s+',' + convert(varchar(10),id),convert(varchar(10),id)) from ta --from (select top 100 percent id from ta order by id) as a
select @s