各位大虾:
小弟,遇到一个这样的问题,有一张表,我将表中的ID让其自动生成,假如表中现在ID为1 2 3 4 5 6 7 8 9 10 这样10条,我现在把8 9 10 这三条记录删除,下一个自动生成的ID应该为11.我现在想在剩下的1 2 3 4 5 6 7 这7条获得下一条记录的ID为11.请问有什么方法可以获得ID吗???????
谢谢!!!!!!!!!!!
小弟,遇到一个这样的问题,有一张表,我将表中的ID让其自动生成,假如表中现在ID为1 2 3 4 5 6 7 8 9 10 这样10条,我现在把8 9 10 这三条记录删除,下一个自动生成的ID应该为11.我现在想在剩下的1 2 3 4 5 6 7 这7条获得下一条记录的ID为11.请问有什么方法可以获得ID吗???????
谢谢!!!!!!!!!!!
create table tbmaxcol
(
maxvalue int default 0
)
insert into tbmaxcol select 0
加入一条记录时把当前的id更新到表中
create trigger on tb from insert
as
update tbmaxcol set maxvalue = @@IDENTITY
insert into # select 'ccc'
union all select 'sss'
union all select 'dddd'
union all select 'nnnnn'
delete #
insert into # select 'cs'
union all select 'dn'
delete #
print IDENT_CURRENT('#')+1
如果是identity(1,1),那么你说的那种情况,下一条记录就是11啊
用IDENT_CURRENT( 'table_name' )函数,可以满足楼主的要求!
create table tt(id int identity,name varchar(10))
insert into tt select 'aa'
insert into tt select 'bb'
insert into tt select 'cc'
insert into tt select 'dd'
insert into tt select 'ee'
insert into tt select 'ff'
insert into tt select 'gg'
insert into tt select 'hh'
delete from tt where id>6
select ident_current('tt') --显示当前最大标识列号码
--8
如果表上有触发器(对其他表进行插入数据)用select scope_identity(),或者select IDENT_CURRENT( 'table_name' )我的理解,不知道能不能帮助楼主?
select SCOPE_IDENTITY( ) 或 IDENT_CURRENT( 'table_name' )也行在具体会话、作用于有点区别而已
(
id int identity(1,1),
varname varchar(10))
delete tableidentityinsert into tableidentity
select 'AAAA' union all
select 'DDDD' union all
select 'CCCC' union all
select 'DDTTT' union all
select 'DDDDDE' union all
select 'AAEEEE' union all
select 'AFFFF' union all
select 'AWWWW'select *from tableidentitydelete tableidentity where id in (6,7,8)select scope_identity() +1
create table tableidentity
(
id int identity(1,1),
varname varchar(10)
)insert into tableidentity
select 'AAAA' union all
select 'DDDD' union all
select 'CCCC' union all
select 'DDTTT' union all
select 'DDDDDE' union all
select 'AAEEEE' union all
select 'AFFFF' union all
select 'AWWWW'删除8910这三条数据
select identity(int,1,1) id,varname into t2 from tableidentitydrop table tableidentityselect id,varname into tableidentity from t2select * from tableidentity
以前我碰過這種問題,我是用觸發器解決辦的,tt表是用來存儲被刪的id,你自己看看,希望對
你有用create table test
(
id integer ,
name varchar(10)
)insert into test
select '1','zhang' union all
select '2','liang' union all
select '3','hong' union all
select '4','liu' union all
select '5','huang' union all
select '6','cheng' union all
select '7','zhao' union all
select '8','zhou' union all
select '9','wang'
create table tt
(
id integer
)
select * from test
create trigger test_delete on test after DELETE
as
begin
insert into tt select id from DELETED
end
create trigger test_insert on test after insert
as
declare @count integer
set @count=(select count(*)from tt)
if @count=0
begin
update test set id=(select max(id)+1 from test) where id =(select id from inserted)
end
else
begin
update test set id=(select min(id) from tt) where id=(select id from inserted)
delete tt where id =(select min(id) from tt)
enddelete test where id in (3,5)
insert into test values(10,'hongliang')
一個最小的id,當作當前id,然後從tt表把訪id刪除,如果tt表是空的話,則test表中max(id)+1
如果是的话,为什么不按id将次顺序排序呢一下呢,那样得到的第一个元组的第一个字段就是最大的啊。
当然,用一个变量来记录可能在操作数据库上会省些处理吧。