数据库表如下:
Reports 字段如下:
RepRkey RepTitle RepRe RepCreator
Rep2012072701 测试报告1 测试电容耐压 susan
如何通过存储过程生成一串编码:Rep20120727xx。(Rep2012072701 , Rep2012072702 .....)
编码规则如下:
1、Rep文件类型编码
2、20120727日期编码
3、01是流水号,如果当天没有记录就是01,如果有记录的话,获取当前编码加1.
Reports 字段如下:
RepRkey RepTitle RepRe RepCreator
Rep2012072701 测试报告1 测试电容耐压 susan
如何通过存储过程生成一串编码:Rep20120727xx。(Rep2012072701 , Rep2012072702 .....)
编码规则如下:
1、Rep文件类型编码
2、20120727日期编码
3、01是流水号,如果当天没有记录就是01,如果有记录的话,获取当前编码加1.
as
begin
declare @s char(13),
@sd char(8),
@ms char(13),
@si char(2)
select @sd = replace(convert(varchar(10),getdate(),120),'-','')
if exists (select 1 from Reports where Reports = 'Rep'+ @sd + '01')
begin
select @ms = max(RepRkey) from Reports where substring(RepRkey,4,8) = @sd
select @si = right('0' + convert(varchar(2), convert(tinyint,right(@ms,2)) + 1),2)
select @s = left(@ms,len(@ms)-2)+@si
end
else
begin
select @s = 'Rep'+ @sd + '01'
endselect @s as NewRepRkeyend
set @ocodeno='009'
select top 1 @MaxId = transid from ec_billhead
where transid like '%'+@ocodeno+SUBSTRING(CONVERT(varchar(100),getdate(),112),3,6)+'%' order by transid desc
if @MaxId is null
begin
set @id =@ocodeno+SUBSTRING(CONVERT(varchar(100),getdate(),112),3,6)+'00001'
set @no = SUBSTRING(CONVERT(varchar(100),getdate(),112),3,6)+'00001'
end
else
begin
set @no =Convert(varchar(100),(CONVERT(bigint,SUBSTRING(@MaxId,4,11))+1))
set @id =@ocodeno+@no
end
print(@no)
print(@id)
以前写的生成最大编号, 关键就在于如何对 varchar类型 还有数据的拼接
declare @dt datetime='2012-8-3'
select 'Rep'+replace(convert(varchar(10),@dt,120),'-','')+right(cast(cast(SUBSTRING(isnull(max(RepRKey),'Rep'+replace(convert(varchar(10),@dt,120),'-','')+'00'),12,2) as int)+101 as CHAR(3)),2)
From Reports where RepRKey like 'Rep'+replace(convert(varchar(10),@dt,120),'-','')+'%'