用游标可以实现
declare @temp varchar(10)
declare @i int
declare cur_test cursor
for
select column_name from tablename
open cur_test
set @i=0
fetch next from cur_test into @temp
while @@fetch_status=0
begin
set @i=@i+1
print CONVERT(varchar(30), @i)+' '+@temp
fetch next from cur_test into @temp
end
--创建得到最大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('张五','张七','张八','张十')