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) */
FROM master..spt_values 这个 master..spt_values是什么意思啊.求指教!
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)
*/
这个 master..spt_values是什么意思啊.求指教!
一般用type为P的number字段来取连续数字的系统表,不过有个缺陷就是只能取到2047。