34,838
社区成员




select sno+1
from t
where sno+1 not in (select sno from t)
----------------
有错误,缺一个号码还可以,但是缺多个号码就查询不出来
2楼跟8楼的不错
create table tb(id varchar(10))
insert into tb values('0001')
insert into tb values('0002')
insert into tb values('0003')
insert into tb values('0004')
insert into tb values('0006')
insert into tb values('0007')
insert into tb values('0008')
insert into tb values('0009')
insert into tb values('0010')
insert into tb values('0012')
go
select right('0000'+cast(id - 1 as varchar),4) as id
from tb aa
where (cast(id as int) - (select max(cast(id as int)) from tb where cast(aa.id as int) > cast(id as int))) > 1
drop table tb
/*
id
--------
0005
0011
(所影响的行数为 2 行)
*/
--参考
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO
--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 1 1
a 4 5
b 2 4
create table tb(id varchar(10))
insert into tb values('0001')
insert into tb values('0002')
insert into tb values('0003')
insert into tb values('0004')
insert into tb values('0006')
insert into tb values('0007')
insert into tb values('0008')
insert into tb values('0009')
insert into tb values('0010')
insert into tb values('0012')
go
select *
from tb aa
where (cast(id as int) - (select max(cast(id as int)) from tb where cast(aa.id as int) > cast(id as int))) > 1
drop table tb
/*
id
----------
0006
0012
(所影响的行数为 2 行)
*/
create table os(bh varchar(4))
insert into os select '0001'
insert into os select '0002'
insert into os select '0003'
insert into os select '0004'
insert into os select '0006'
insert into os select '0007'
insert into os select '0008'
insert into os select '0009'
insert into os select '0010'
insert into os select '0012'
declare @sql varchar(100)
select @sql='select top '+max(bh)+'id=identity(int,1,1) into ## from sysobjects' from os
exec(@sql)
select right('0000'+ltrim(id),4) from ## where not exists(select 1 from os where cast(bh as int)=id)
--参考
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',NULL
select *
from @t aa
where (a - (select max(a) from @t where aa.a > a)) > 1
/*
a b flag
----------- ------ -----------
5 four NULL
10 six NULL
*/