给你个思路吧。建立一个临时表:select identfy(1,1) as id,convert(int,right(字段,3)) as NumberS into #t
现在求要插入的号码:
create function Search
as
return varchar(10)
begin
declare @AAAX int
if exists(select top 1 id from #t where id != NumberS order by id asc)
select @AAAX =top 1 id from #t where id != NumberS order by id asc
else
select @AAAX =max(NumberS)+1
Set @AAAX =convert(varchar(10),@AAAX)
if len(ltrim(rtrim(@AAAX )))=3
Set @AAAX=@AAAX
else if len(ltrim(rtrim(@AAAX )))=2
Set @AAAX='0'+@AAAX
else
Set @AAAX='00'+@AAAX
return 'AAA'+@AAAX--就是你要插入的编号。
end
现在求要插入的号码:
create function Search
as
return varchar(10)
begin
declare @AAAX int
if exists(select top 1 id from #t where id != NumberS order by id asc)
select @AAAX =top 1 id from #t where id != NumberS order by id asc
else
select @AAAX =max(NumberS)+1
Set @AAAX =convert(varchar(10),@AAAX)
if len(ltrim(rtrim(@AAAX )))=3
Set @AAAX=@AAAX
else if len(ltrim(rtrim(@AAAX )))=2
Set @AAAX='0'+@AAAX
else
Set @AAAX='00'+@AAAX
return 'AAA'+@AAAX--就是你要插入的编号。
end
go
--测式
insert into test (FLD)
select '01'
insert into test (FLD)
select '02'
insert into test (FLD)
select '03'
insert into test (FLD)
select '04'update test set idno='AAA'+RIGHT(cast(id+1000 as varchar(4)),3)
--结果
/*
ID idno fld
----------- ---------- ----------
1 AAA001 01
2 AAA002 02
3 AAA003 03
4 AAA004 04(所影响的行数为 4 行)
*/
go
create table test ([ID] [int] IDENTITY (1, 1) NOT NULL constraint pk_id primary key ,idno varchar(10),fld varchar(10))
go--测式
insert into test (FLD)
select '01'
insert into test (FLD)
select '02'
insert into test (FLD)
select '03'
insert into test (FLD)
select '04'delete from test where id=2
update test set idno='AAA'+RIGHT(cast((select count(1) from test where id<=a.id)+1000 as varchar(4)),3) from test a
select * from test
--结果
ID idno fld
----------- ---------- ----------
1 AAA001 01
3 AAA002 03
4 AAA003 04(所影响的行数为 3 行)*/
go
--连续的结果集时
insert into @a
select 'AAA001' union
select 'AAA002' union
select 'AAA003' union
select 'AAA004' union
select 'AAA005' union
select 'AAA006' union
select 'AAA007'
--处理语句
declare @maxNum varchar(20)
select @maxNum=(select top 1 (select 'AAA'+right('000'+convert(varchar,count(*)+1),3) from @a where myid<a.myid)
from @a a
where myid<>(select 'AAA'+right('000'+convert(varchar,count(*)+1),3) from @a where myid<a.myid)
order by myid)
select @maxNum= case when @maxNum is null then (select 'AAA'+right('000'+convert(varchar,convert(int,right(isnull(max(myid),'AAA000'),3))+1),3) from @a) else @maxNum end
select @maxNum
/*
结果
-------------
AAA008
*/
--不连续的结果时
delete from @ainsert into @a
select 'AAA001' union
select 'AAA002' union
select 'AAA004' union
select 'AAA007' union
select 'AAA008' union
select 'AAA010' union
select 'AAA012'select @maxNum=(select top 1 (select 'AAA'+right('000'+convert(varchar,count(*)+1),3) from @a where myid<a.myid)
from @a a
where myid<>(select 'AAA'+right('000'+convert(varchar,count(*)+1),3) from @a where myid<a.myid)
order by myid)
select @maxNum= case when @maxNum is null then (select 'AAA'+right('000'+convert(varchar,convert(int,right(isnull(max(myid),'AAA000'),3))+1),3) from @a) else @maxNum end
select @maxNum
/*
结果
---------
AAA003
*/