--自已做标识列的例子:--创建得到最大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 tb--删除环境 drop table tb drop function f_getid
如果要求不高,可以将字段设置成标识字段identity.缺点是删除记录后,记录号不会恢复.
select identity(int,1,1)as id a,b,c into #temp from yourtable select * from #temp
是当前的行数吧。 如果有id唯一标志你可以:select count(*) 行号 from t where id<= (select top 1 id from t where col1='a' order by 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 tb--删除环境
drop table tb
drop function f_getid
select * from #temp
如果有id唯一标志你可以:select count(*) 行号 from t
where id<=
(select top 1 id from t where col1='a' order by id)