关于此贴中的问题延续:
http://topic.csdn.net/u/20080501/16/6763092b-cbd5-4237-8f66-c75a10970467.htmlcreate table test
( id int primary key )
goinsert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (18)
insert into test values (19) 若要找出中间漏的数字,中间只漏一个数字可以通过此语句获得
select id+1 from test where id+1 not in (select id from test)但若是中间差连续的多个数字该怎么获得?
http://topic.csdn.net/u/20080501/16/6763092b-cbd5-4237-8f66-c75a10970467.htmlcreate table test
( id int primary key )
goinsert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (18)
insert into test values (19) 若要找出中间漏的数字,中间只漏一个数字可以通过此语句获得
select id+1 from test where id+1 not in (select id from test)但若是中间差连续的多个数字该怎么获得?
( id int primary key )
go
insert into #test values (1 )
insert into #test values (2 )
insert into #test values (3 )
insert into #test values (4 )
insert into #test values (8 )
insert into #test values (9 )
insert into #test values (11)
insert into #test values (18)
insert into #test values (19) declare @max int
select @max=max(id) from #test
select top(@max) id=identity(int,1,1) into # from sys.columns
select a.* from # a left join #test b on a.id=b.id where b.id is nulldrop table #,#test
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (18)
insert into test values (19)
go
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b select id from tmp m where id <= (select max(id) from test) and id not in (select id from test)drop table test,tmp/*
id
-----------
5
6
7
10
12
13
14
15
16
17(所影响的行数为 10 行)
*/select
create table test ( id int primary key )
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (18)
insert into test values (19)
go
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b
drop table test,tmpselect a.* from tmp a left join test b
on a.id=b.id where b.id is null and a.id <=(select max(id) from test)
/*
5
6
7
10
12
13
14
15
16
17
*/