create table tb(aa varchar(10))
insert into tb values('000020005')
insert into tb values('000020004')
insert into tb values('000020011')
godeclare @max as varchar(9)
select @max = max(right(aa,4)) from tb
declare @l5 as varchar(5)
select @l5 = left(aa,5) from tb where right(aa,4) = @max
set @max = @l5 + right('0000' + cast(cast(@max as int) + 1 as varchar),4)
insert into tb values(@max)
select * from tb
drop table tb/*
aa
----------
000020005
000020004
000020011
000020012(所影响的行数为 4 行)
*/
insert into tb values('000020005')
insert into tb values('000020004')
insert into tb values('000020011')
godeclare @max as varchar(9)
select @max = max(right(aa,4)) from tb
declare @l5 as varchar(5)
select @l5 = left(aa,5) from tb where right(aa,4) = @max
set @max = @l5 + right('0000' + cast(cast(@max as int) + 1 as varchar),4)
insert into tb values(@max)
select * from tb
drop table tb/*
aa
----------
000020005
000020004
000020011
000020012(所影响的行数为 4 行)
*/
insert into tb values('000020005')
insert into tb values('000020004')
insert into tb values('000020011')
gocreate procedure my
as
begin
declare @max as varchar(9)
select @max = max(right(aa,4)) from tb
declare @l5 as varchar(5)
select @l5 = left(aa,5) from tb where right(aa,4) = @max
set @max = @l5 + right('0000' + cast(cast(@max as int) + 1 as varchar),4)
insert into tb values(@max)
end
goexec myselect * from tb
drop table tb
drop procedure my/*
aa
----------
000020005
000020004
000020011
000020012
(所影响的行数为 4 行)
*/