小问题,写个存储过程来完成
假设是表A的字段no需
create procedure p_getno @no char(13) output
as
declare @date datetime,@lvstr char(10)
set @date=getdate()
set @lvstr='LV'+str(year(@date),4)+right(str(100+month(@date),3),2)+right(str(100+day(@date),3),2)
if exists(select * from a where no like @lvstr+'%')
begin
select top 1 @no=no from a where no like @lvstr+'%' order by no desc
select @no=@lvstr+right(str(1001+convert(int,right(@no,3)),4),3)
end else
select @no=@lvstr+'001'
if object_id('test1') is not null
drop table test1
create table test1(
id varchar(20),
sub int
)
create function adddate()
returns varchar(200)
as
begin
declare @i varchar(20),@a int,@date varchar(200)
set @i=substring(convert(varchar(20),getdate(),112),3,6);
set @date='lv070903001'
select @date=('lv'+@i+
case when convert(int,right(isnull(id,'00000000'),3)+1)<10 then '00'+convert(varchar(10),right(id,1)+1)
when convert(int,right(isnull(id,'00000000'),3)+1)<100 then '0'+convert(varchar(20),right(id,2)+1)
when convert(int,right(isnull(id,'00000000'),3)+1)<1000 then convert(varchar(20),right(id,3)+1) end
)
from test1
return @date
end
declare @i int
set @i=0
while(@i<200)
begin
insert into test1
select dbo.adddate(),80
set @i=@i+1
end
新建个函数
create function f_getid(@date datetime)
returns varchar(11)
as
begin
declare @str varchar(11)
select @str=isnull(max(lvid),'LV'+right(datename(year,@date),2)+right(datename(month,@date),2)+right('00'+datename(day,@date),2)+'000')
from tab1
where lvid like 'LV'+right(datename(year,@date),2)+right(datename(month,@date),2)+right('00'+datename(day,@date),2)+'%'
select @str=left(@str,8)+right('000'+convert(varchar(3),convert(int,right(@str,3))+1),3)
return (@str)
end