有一列数据 1至100 中间可能不连接,如何找出不连续的数字出来?

[lang_csdn]

表结构
id
1
3
4
5
6
7
12

找出中间的 2 8 9 10 11

谢谢!

[filebat]

加一个带序号列的临时表可以吗？

[filebat]

--测试数据
create table ta(id int)
insert ta select 1 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7
union all select 12
select top 100 id=identity(int)
into #tt
from syscolumns
--查询
select t1.id
from #tt as t1 left join ta as t2
on t1.id=t2.id
where t1.id<(select max(id) from ta)
and t2.id is null

--清除
drop table #tt
drop table ta

[vivianfdlpw]

declare @tb table
(
id int
)
insert @tb
select 1 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 12

declare @t table(num int)
declare @tmp int
set @tmp=1
while @tmp<=(select max(id) from @tb)
begin
insert @t select @tmp
set @tmp=@tmp+1
end

--查询
select num
from @t t
where not exists(select 1 from @tb where id=t.num)

--结果
/*
num
-----------
2
8
9
10
11

(5 row(s) affected)
*/

[健者天行]

declare @T table(id int)
insert into @t
select 1
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 12


select A.id+1 as 断号开始,B.id-1 as 断号结束,B.id-A.id-1 as 断号间隔
from @t A,@t B
where A.id<B.id
and not exists (select 1 from @t where id>A.id and id<B.id)
and not exists(select 1 from @t where id=A.id+1)
and not exists(select 1 from @t where id=B.id-1)


select A.id+T1.ID from
(
select A.id*1000+B.id*100+C.id*10+D.id as id from
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) A,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) B,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) C,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) D
) A,@t T1,@t T2
where T1.id<T2.id
and not exists(select 1 from @t where id>T1.id and ID<t2.id)
and A.id>=1 and A.id+t1.ID<T2.id
ORDER BY A.id+T1.ID

[chenchangfu]

select t.Id
from (select A.Id * 10 + B.Id + 1 as Id
select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) A,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) B) C
where t.Id < (select Max(Id) from t) and
t.id not in (select id from t)

[山林73]

表结构ta
id
1
3
4
5
6
7
12

找出中间的 2 8 9 10 11

增加字段 ,idx int,
create index ix_ta_id on ta(id)

declare @i as int
set @i=1
update ta set idx=@i, @i=id +1
from ta with( INDEX ( tx_ta_id ))

select * from ta where id<> idx

[gimy007]

加一个带序号列的临时表,与它做连接就可以出来了

[WZZ]

create table ta(id int)
insert ta select 1 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7
union all select 12

declare @ int
select @ = max(id) from ta

select top 100 identity(int,1,1) as id into #t from sysobjects

select * from #t as a where not exists(select * from ta where id = a.id) and id <= @

drop table ta, #t

[山林73]

select identity(int,1,1) as i, id
into #ts
from ta order by id

select a.id from #ta a, #ta b where b.i=a.i+1 and b.id<>a.id+1

[山林73]

select identity(int,1,1) as i, id
into #ts
from ta order by id

select a.id from #ts a, #ts b where b.i=a.i+1 and b.id<>a.id+1