select c from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where c between (select min(node) from test) and (select max(node) from test) and c not in (select node from test) order by c
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where c between (select min(node) from test) and (select max(node) from test) and c not in (select node from test) order by c
where id not in (select node from test)
select * from (Select top 99 (select sum(1) from sysobjects where name<= a.name)+1100 as id from sysobjects a) aa
where id not in (select node from test)
select * from (Select top 99 (select sum(1) from sysobjects where name<= a.name)+1100 as id from sysobjects a) aa
where id not in (select node from test)
select distinct id from A left join test on A.id=test.node where test.node is null
select * from (Select top 99 (select sum(1) from sysobjects where name<= a.name)+1100 as id from sysobjects a) aa
where id not in (select node from test)
create table num2(node int check (node between 1101 and 1199))--建表declare @num int
set @num=1101
while @num<1200
begin
exec('insert into num2 values('+@num+')')
set @num=@num+1
end--插入1101-1199的数字
select * from num2select* from num2 where not exists( select * from num where num2.node=num.node)
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where c<(select max(node) from test) and c not in (select node from test) order by c
http://expert.csdn.net/Expert/topic/2437/2437014.xml?temp=.67857
交流]自增号
declare @tb table(id int identity(1,1),ygbh varchar(10),name varchar(20),bmbh varchar(4))
insert into @tb
select 'A00001','a 张三','A'
union all select 'A00002','b 张三','A'
union all select 'A00004','c 张三','A'
union all select 'A00005','d 张三','A'
union all select 'A00007','e 张三','A'
union all select 'A00009','f 张三','A'
union all select 'A00011','g 张三','A'
union all select 'A00012','h 张三','A'
union all select 'B00001','a 李四','B'
union all select 'B00009','b 李四','B'
union all select 'B00011','c 李四','B'
union all select 'B00012','d 李四','B'
union all select 'C00002','a 王五','C'
union all select 'C00011','c 王五','C'
union all select 'C00012','d 王五','C'--生成临时表,得到员工的数字编号
select ygbh=cast(right(ygbh,len(ygbh)-len(bmbh)) as int)
,bhlen=len(ygbh)-len(bmbh)
,fmt=replace(space(len(ygbh)-len(bmbh)),' ','0')
,bmbh
into #tb from @tb order by bmbh,ygbh--到每个缺号的开始编号
select id=identity(int,1,1),bmbh,bhlen,fmt
,bid=ygbh+1
into #tb1 from #tb a
where not exists(select 1 from #tb where ygbh=a.ygbh+1 and bmbh=a.bmbh)--得到每个缺号的结束编号
select id=identity(int,0,1),bmbh,bhlen,fmt
,eid=ygbh-1
into #tb2 from #tb a
where not exists(select 1 from #tb where ygbh=a.ygbh-1 and bmbh=a.bmbh)--显示缺号结果
select 部门编号=a.bmbh
,缺号记录=a.bmbh+right(a.fmt+cast(a.bid as varchar),a.bhlen)
+case a.bid when b.eid then ''
else ' ~ '+b.bmbh+right(b.fmt+cast(b.eid as varchar),b.bhlen) end
from #tb1 a inner join #tb2 b
on a.id=b.id and a.bmbh=b.bmbh--删除临时表
drop table #tb,#tb1,#tb2