--创建得到最大id的函数 create function f_getid() returns varchar(1000) as begin declare @id int select @id= substring(max(id),5,len(max(id))) from tb
set @id=isnull(@id,0)+1 return(@id) end go--创建表 create table tb(id varchar(1000) default convert(varchar(4), year(getdate()))+dbo.f_getid(),name1 varchar(10)) go
--插入记录测试 insert into tb(name1) values('张三') insert into tb(name1) values('张四') insert into tb(name1) values('张五') insert into tb(name1) values('张六') insert into tb(name1) values('张七') insert into tb(name1) values('张八') insert into tb(name1) values('张九') insert into tb(name1) values('张十')--显示插入的结果 select * from tb--删除部分记录 delete from tb where name1 in('张五','张七','张八','张十')--显示删除后的结果 select * from tb--删除环境 drop table tb drop function f_getid
create function f_getid()
returns varchar(1000)
as
begin
declare @id int
select @id= substring(max(id),5,len(max(id))) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id varchar(1000)
default convert(varchar(4),
year(getdate()))+dbo.f_getid(),name1 varchar(10))
go
--插入记录测试
insert into tb(name1) values('张三')
insert into tb(name1) values('张四')
insert into tb(name1) values('张五')
insert into tb(name1) values('张六')
insert into tb(name1) values('张七')
insert into tb(name1) values('张八')
insert into tb(name1) values('张九')
insert into tb(name1) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name1 in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid