如何让编号自动增加？并能填补以前删除的号码？

Question

自己维护编号字段，用下面的select语句取最小的可用编号select isnull(min(id), 0) + 1 from table t where not exists (select 1 from table where id = t.id + 1)

Answer 1

--自已做标识列的例子:--创建得到最大id的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid(),name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tbgo
--删除环境
drop table tb
drop function f_getid

Answer 2

--上面是删除后编号自动重排的例子,下面是楼主要求的--自已做标识列的例子,不自动重排编号,而是自动补号:--创建得到最大id的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
if @id is null
set @id=1
else
begin
declare @id1 int
select @id1=min(id) from tb a where id<>@id and not exists(select 1 from tb where id=a.id+1)
if @id1 is not null set @id=@id1
set @id=@id+1
endlb_re:
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid(),name varchar(10))
go
--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--再次插入记录
insert into tb(name) values('李一')
insert into tb(name) values('李二')--显示插入的结果
select * from tbgo
--删除环境
drop table tb
drop function f_getid

Answer 3

select (case when isnull((select min(id) as id from 物品 where 编号=1),0)=0 then 1 else isnull(min(编号),0)+1 end) as ID from 物品 a where not exists(select 1 from 物品 where 编号=a.编号+1)http://expert.csdn.net/Expert/topic/2338/2338683.xml?temp=.6893274

Answer 4

--e.g.create table #t(id int)
insert #t 
select 1
union all select 2
union all select 3
union all select 4select * from #tdelete #t where id=2insert #t select
(
select min(id)+1 from #t a where not exists(select 1 from #t where id=a.id+1) --得到最小去缺号
)select * from #tdrop table #t

Answer 5

--改zjcxc(邹建)的：
--自已做标识列的例子,不自动重排编号,而是自动补号:--创建得到最大id的函数
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
if (@id is null) or (not exists(select 1 from tb where id=1))  --稍微修改此处
set @id=1
else
begin
declare @id1 int
select @id1=min(id) from tb a where id<>@id and not exists(select 1 from tb where id=a.id+1)
if (@id1 is not null)  set @id=@id1
set @id=@id+1
endlb_re:
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid(),name varchar(10))
go--插入记录测试
insert into tb(name) values('张大')
insert into tb(name) values('张二')
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张大','张七','张八','张十')--显示删除后的结果
select * from tb--再次插入记录
insert into tb(name) values('李一')
insert into tb(name) values('李二')
insert into tb(name) values('李三')
insert into tb(name) values('李四')
insert into tb(name) values('李五')--显示插入的结果
select * from tbgo
--删除环境
drop table tb
drop function f_getid

Answer 6

to: prcgolf(小鸟) 我的第二个例子就已经是自动补号的.

Answer 7

to:zjcxc(邹建)
你的对id空缺1的不行啊。
加个条件即可啊

Answer 8

只要一个SQL语句：
SELECT TOP 1 ID+1 FROM TEST WHERE ID NOT IN (SELECT ID-1 FROM TEST)

Answer 9

还有一种只针对不丰满的队列的写法，也是一句：
SELECT TOP 1 'id' = CASE
               WHEN id%2=0 THEN id-1
               WHEN id%2=1 THEN id+1
             END   FROM TEST WHERE id NOT IN (SELECT 'id' = 
      CASE 
         WHEN id%2=0 THEN id-1
         WHEN id%2=1 THEN id+1
      END  FROM TEST )