tb
id
0001
0002
0006
0008
0009
0010
0110
0333
找出中间断号的ID
0003
0004
0005
0007
0011
...
0109
...
0332
------解决方案--------------------
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id varchar(4))
insert into [tb]
select '0001' union all
select '0002' union all
select '0006' union all
select '0008' union all
select '0009' union all
select '0010' union ALL
select '0110' union all
select '0333'
select * from [tb]
SELECT number
FROM ( SELECT RIGHT('000' + CONVERT(VARCHAR, A.number), 4) AS number ,
b.id
FROM master..spt_values A
LEFT JOIN tb B ON A.number = B.id
WHERE A.type = 'P'
AND A.number > 0
AND A.number <(SELECT MAX(id) FROM tb)
) A
WHERE A.id IS NULL
/*
0003
0004
0005
0007
...
0330
0331
0332*/
--最大检测到2048
------解决方案--------------------
create table tb(id varchar(10))
insert into tb(id)
select '0001' union all
select '0002' union all
select '0006' union all
select '0008' union all
select '0009' union all
select '0010' union all
select '0110' union all
select '0333'
select right('0000'+rtrim(number),4) 'ID'
from master.dbo.spt_values
where type='P' and number
between (select cast(min(id) as int) from tb)
and (select cast(max(id) as int) from tb)
and not exists
(select 1 from tb where id=right('0000'+rtrim(number),4))
/*
ID
--------
0003
0004
0005
0007
0011
0012
0013
0014
0015
0016
.
.(略)
.
.
0327
0328
0329
0330
0331
0332
(325 row(s) affected)
*/
------解决方案--------------------
系统内部的一个表。 2000就有，现在也还在。
2014 版本建议用序列。
------解决方案--------------------
一般用type为P的number字段来取连续数字的系统表，不过有个缺陷就是只能取到2047。