IDENTITY(函数)
只用在带有 INTO table 子句的 SELECT 语句中,以将标识列插入到新表中。尽管类似,但是 IDENTITY 函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。语法
IDENTITY ( data_type [ , seed , increment ] ) AS column_name
只用在带有 INTO table 子句的 SELECT 语句中,以将标识列插入到新表中。尽管类似,但是 IDENTITY 函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。语法
IDENTITY ( data_type [ , seed , increment ] ) AS column_name
select * from #t
drop table #t
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 (select count(*) from t where Sn<=a.Sn) 流水号,* from t a order by Sn如果Sn1,Sn2构成唯一,那么
select (select count(*) from t where Sn1<=a.Sn1 and Sn2<=a.Sn2) 流水号,* from t a order by Sn1,Sn2
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
endclose cur_test
deallocate cur_test结果如下
1 admin
2 swufe
3 lyh