SQL自动计算带字母的编号

 
CREATE TABLE Cargo( 
 [CargoCode] [varchar](16) , 
)  
 
Insert Into Cargo(CargoCode) Select 'aa20110827001'
Insert Into Cargo(CargoCode) Select 'aa20110827002'
Insert Into Cargo(CargoCode) Select 'aa20110827003'
Insert Into Cargo(CargoCode) Select 'aa20110827005'
 
Declare @CargoCode  varchar(16) 
Declare @SQL   NVarchar(4000) 
 
Set @SQL = 'Select  @CargoCode = ''aa' + CONVERT(varchar(8), GETDATE(), 112)+ ''' + Right(''000'' + Ltrim(Rtrim(cast(Right(MAX(CargoCode), 3)+1   as   varchar(3)))), 3)'
Set @SQL = @SQL + ' From Cargo'
Set @SQL = @SQL + ' where CargoCode Like ''aa' + CONVERT(varchar(8), GETDATE(), 112) + '%'''
 
Exec sp_executesql @SQL,N'@CargoCode Varchar(16) output',@CargoCode output      
 
Select @CargoCode 
 
Drop Table Cargo 
 
 本文出自“cnming”