SQL中实现文本形式的流水编号

创建流水编号的例子

用户填写论文信息，填写好后，执行上报操作，生成论文编号，论文编号格式为 LW-2011-00001 ,其中后五位为根据上报顺序自动从小到大生成的流水号。以下为实现的办法。

--初始开始--
 --申请表
 CREATE TABLE Apply
 (
 ApplyID INT IDENTITY(1,1) NOT NULL,
 Code VARCHAR(30),
 ApplyUser NVARCHAR(30)
 )
 GO 
--初始结束 

--创建编码开始  
 --取得申报编码(每个学段学科编码起始号从0001开始)
 CREATE FUNCTION [dbo].[f_getCode]
 (@ApplyID INT)
 RETURNS VARCHAR(30)
 AS
 BEGIN
 --判断申请材料是否已有编号，如果有则返回原有编号
 DECLARE @ReVal VARCHAR(30)
 SELECT TOP 1 @Reval=code FROM Apply WHERE ApplyID=@ApplyID
 IF LEN(ltrim(ISNULL(@Reval,'')))>0
 RETURN @ReVal;  
 --申请材料无编号,创建编号
 ----生成除流水号外的编号
 DECLARE @CodeModel NVARCHAR(30)
 SELECT TOP 1 @CodeModel=Code FROM Apply WITH(TABLOCKX,HOLDLOCK) WHERE LEN(ISNULL(ltrim(Code),''))>0
 IF(LEN(ISNULL(@CodeModel,''))>0) --有记录取编号规则
 BEGIN
  set @CodeModel=substring(@CodeModel,1,len(@CodeModel)-charindex('-',reverse(@CodeModel)))+'-'
 END
 ELSE ----无记录，生成编号规则
 BEGIN 
  SET @CodeModel='LW-'+CONVERT(VARCHAR(4),YEAR(GETDATE()))+'-';
 END
 ----生成流水号
 DECLARE  @MaxCode int,@Sequence VARCHAR(10)
 SELECT @MaxCode=MAX(case when charindex('-',reverse(code))-1>0 then cast(reverse(left(reverse(code),charindex('-',reverse(code))-1)) as int) else 0 end)
 FROM Apply WITH(TABLOCKX,HOLDLOCK)
 IF(ISNULL(@MaxCode,0)>0) --本项目有记录，在最大编码后加1
 BEGIN
  set @Sequence=ISNULL(replicate('0',5-len(@MaxCode+1)),'')+cast((@MaxCode+1) as varchar(10));
 END
 ELSE
 begin   
   SELECT @MaxCode=MAX(case when charindex('-',reverse(code))-1>0 then cast(reverse(left(reverse(code),charindex('-',reverse(code))-1)) as int) else 0 end)
  FROM Apply WITH(TABLOCKX,HOLDLOCK)
  IF(ISNULL(@MaxCode,0)>0) --有记录，在最大编码后加1
  BEGIN
   set @Sequence=ISNULL(replicate('0',5-len(@MaxCode+1)),'')+cast((@MaxCode+1) as varchar(10));--五位长度的编号
  END
  else
  begin 
   SET @Sequence='00001' --默认流水号 
  end
 END 
 --生成完整编号
 SET @ReVal=@CodeModel+@Sequence;
 RETURN @ReVal;
 END
 GO 
--创建编码结束
 

--查看结果

--插入数据
SET IDENTITY_INSERT APPLY ON
INSERT INTO Apply(APPLYid,ApplyUser)
SELECT APPLYid,ApplyUser FROM (
SELECT 1 as APPLYid ,'张三' AS ApplyUser
UNION ALL SELECT 2 as APPLYid ,'李四' AS ApplyUser
UNION ALL SELECT 3 as APPLYid ,'王五' AS ApplyUser
)a
WHERE NOT EXISTS(SELECT applyid FROM APPLY WHERE a.APPLYid=applyid)
SET IDENTITY_INSERT APPLY OFF 
GO   
--创建编号

UPDATE APPLY SET code=dbo.f_getCode(1) WHERE applyid=1
UPDATE APPLY SET code=dbo.f_getCode(2) WHERE applyid=2
UPDATE APPLY SET code=dbo.f_getCode(3) WHERE applyid=3
--查看生成编号
SELECT * FROM APPLY  
GO