declare @t table(id int identity(1,1),c varchar(20)) insert @t select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' union all select 'f' union all select 'g' delete @t where id=6 select * from @t select number from master..spt_values where type='P' and number<=(select max(id) from @t) and number>=(select min(id) from @t) and number not in(select id from @t)--查询结果 (7 行受影响)(1 行受影响) id c ----------- -------------------- 1 a 2 b 3 c 4 d 5 e 7 g(6 行受影响)number ----------- 6(1 行受影响)
DBCC IND('DB','YOUTABLE',-1) 看看
create Talbe TT(a int,b int)select top 1 * from ( select case when (select count(1) from TT where a=(T.a+1))=0 then T.a+1 end as delV from TT as T ) as A where delV is not null order by DelV
--tryif object_id('[TB]') is not null drop table [TB] create table [TB]([col] int) insert [TB] 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 8 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 18 union all select 19select id=isnull(col,number) from spt_values left join TB on number=col where type='p' and number>0 and number<=(select max(col) from TB) and col is null /* id ----------- 2 9 17(所影响的行数为 3 行)*/drop table TB
if object_id('[TB]') is not null drop table [TB] go create table [TB]([col] int) insert [TB] 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 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 declare @s int set @s=(select MAX(COL) from tb ) --删除测试 delete tb where col=19 select isnull(Min(col)+1,1) from tb k where not exists(select * from tb where k.col+1=col) and col<@s
是否求断号 对表tb select id, id1=(select top 1 id from tb where id>a.id order by id) from tb a where not exists(select * from tb where id+1=a.id)
declare @t table(id int identity(1,1),c varchar(20))
insert @t
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f' union all
select 'g'
delete @t where id=6
select * from @t
select number from master..spt_values
where type='P'
and number<=(select max(id) from @t)
and number>=(select min(id) from @t)
and number not in(select id from @t)--查询结果
(7 行受影响)(1 行受影响)
id c
----------- --------------------
1 a
2 b
3 c
4 d
5 e
7 g(6 行受影响)number
-----------
6(1 行受影响)
看看
(
select
case when
(select count(1) from TT where a=(T.a+1))=0
then
T.a+1
end as delV
from
TT as T
) as A
where delV is not null order by DelV
create table [TB]([col] int)
insert [TB]
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 8 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 18 union all
select 19select id=isnull(col,number) from spt_values left join TB on number=col where type='p' and number>0 and number<=(select max(col) from TB)
and col is null
/*
id
-----------
2
9
17(所影响的行数为 3 行)*/drop table TB
drop table [TB]
go
create table [TB]([col] int)
insert [TB]
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 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19
declare @s int
set @s=(select MAX(COL) from tb )
--删除测试
delete tb where col=19
select isnull(Min(col)+1,1)
from tb k
where not exists(select * from tb where k.col+1=col)
and col<@s
对表tb
select id,
id1=(select top 1 id from tb where id>a.id order by id)
from tb a
where not exists(select * from tb where id+1=a.id)