如何判断数据连号

tyhuang 2008-02-28 10:31:07
请问数据库中有的记录
0001
0002
0003
0004
0006
0007
0008
0009
0010
0012

如何把不是连号的0005和0011找出来
...全文
279 点赞 收藏 14
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
songliu 2008-02-29
11楼的受用了
回复
ojuju10 2008-02-28


select sno+1
from t
where sno+1 not in (select sno from t)

----------------
有错误,缺一个号码还可以,但是缺多个号码就查询不出来

2楼跟8楼的不错

回复
welove1983 2008-02-28
declare @a table(uid int)
insert into @a
select 1
union all
select 2
union all
select 4
union all
select 10

select tid=identity(int,1,1) into #table from sysobjects
select tid from #table where tid <=(select max(uid) from @a a) and tid not in (select uid from @a b)


tid
3
5
6
7
8
9
回复
welove1983 2008-02-28
select tid=identity(int,1,1) into #table from sysobjects
select tid from #table where tid<=(select max(uid) from 表) and tid not in (select uid from 表)
回复
pt1314917 2008-02-28
9楼的对。。。老乌龟的写法只支持缺少一个连号的情况``
回复
wzh1215 2008-02-28
7楼只能针对间隔1个号的情况,如果有两个以上连号不在表中则不正确
如:
0001
0002
0003
0004
0006
0007
0008
0009
0010
0015
回复
ccnh 2008-02-28
create table #temp (a int)
insert #temp values (1)
insert #temp values (2)
insert #temp values (3)
insert #temp values (4)
insert #temp values (6)
insert #temp values (7)
insert #temp values (8)
insert #temp values (9)
insert #temp values (10)
insert #temp values (12)

create table #temp1 (b int)
declare @i int,@j int
select @i=min(a),@j=max(a) from #temp


WHILE @i<=@j
BEGIN
insert #temp1 values (@i)
set @i=@i+1
END

select * from #temp1 where not exists (select 1 from #temp where a=#temp1.b)

回复
dawugui 2008-02-28
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 行)
*/
回复
wzy_love_sly 2008-02-28

--参考
--测试数据
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
回复
dawugui 2008-02-28
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 行)
*/
回复
liuyann 2008-02-28

select sno+1
from t
where sno+1 not in (select sno from t)
== 思想重于技巧 ==
回复
wzh1215 2008-02-28
create table #tb(ID varchar(4))
insert #tb(ID)
select '0001'
union all select '0002'
union all select '0003'
union all select '0004'
union all select '0006'
union all select '0007'
union all select '0008'
union all select '0009'
union all select '0010'
union all select '0011'
union all select '0013'
select top 10000 ID=identity(int,1,1) into #aa from sysobjects a join sysobjects b on 1=1 join sysobjects c on 1=1

select right('0000'+cast(a.ID as varchar(5)),4) from #aa a left join #tb b on a.ID=b.ID where b.ID is null
and a.ID<(select Max(ID) from #tb)
回复
pt1314917 2008-02-28

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)

回复
dawugui 2008-02-28
--参考
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
*/
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-02-28 10:31
社区公告
暂无公告