如何定义一个OR070717001这样的自增编号
一般都是
SID int identity(1,1) primary key,这样产生自增编号的
我想产生为OR070717001这样的自增编号
OrderID varchar(50) primary key default ( 'SR '+convert(varchar,year(getdate()))+convert(varchar,month(getdate()))+convert(varchar,day(getdate()))+convert(varchar,identity(1,1))
OrderID varchar(50) primary key default ( 'SR '+convert(varchar,year(getdate()))+convert(varchar,month(getdate()))+convert(varchar,day(getdate()))+convert(varchar,max(right(OrderID,3))))
上面两个方法都不行.高手来帮忙啊!
[解决办法]
--没有你这种写法，可改为用计算字段，如：
Create table t(SID int identity(1,1) primary key ,
OrderID as ( 'SR '+convert(varchar,year(getdate()))+
convert(varchar,month(getdate()))+
convert(varchar,day(getdate()))+convert(varchar,SID)), Ch char(1))
[解决办法]
--境
declare @t table(id varchar(11))
insert @t select 'OR070611003 '
union all select 'OR070717004 '
--生成流水
select 'NewId ' = 'OR ' + right(replace(convert(varchar(10), getdate(), 120), '- ', ' '), 6)
+ right( '000 ' + ltrim(isnull(cast(right(max(id), 3) as int), 0) + 1), 3)
from @t
where right(replace(convert(varchar(10), getdate(), 120), '- ', ' '), 6) = substring(id, 3, 6)
--果
/*
NewId
--------------------
OR070717005
（所影响的行数为 1 行）
*/
-- 'OR070717004 '不存在的果
/*
NewId
--------------------
OR070717001
（所影响的行数为 1 行）
*/
[解决办法]
create table tba(OrderID varchar(50),b int)
----------------------------------
CREATE function new_idw(@a datetime) returns varchar(50)
begin
declare @id varchar(50),@i int
select top 1 @id=OrderID from tba where left(OrderID,8)= 'SR '+convert(varchar(6),@a,12) order by OrderID desc
if @@rowcount=0
return 'SR '+convert(varchar(6),@a,12)+ '001 '
set @i= rtrim(right(@id,3)) +1
set @id=rtrim(@i)
return 'SR '+convert(varchar(6),@a,12)+right( '000 '+@id ,3 )
end
------------------------------------------
insert tba select dbo.new_idw(getdate()),1
insert tba select dbo.new_idw(getdate()),2
insert tba select dbo.new_idw(getdate()+1),3
insert tba select dbo.new_idw(getdate()+1),3
select * from tba
[解决办法]
单据号999张的确比较少了，呵呵，我写了一个，5位的。
参考：
-- 创建表
CREATE TABLE HEAD_NUMINFO(
HEAD_CATEGORY CHAR(3), -- 单据类型前缀
HEAD_DATECHAR(6), -- 生成的6位单据日期号
HEAD_SEQINT-- 单据序列号
PRIMARY KEY(HEAD_CATEGORY,HEAD_DATE)
)
-- 从创建生成单据号存储过程
create procedure makeHeadNumber
@category char(3),-- 单据类型前缀
@strHeadNumber char(14) output-- 返回的单据信息
as
set transaction isolation level repeatable read -- 设置隔离级别
begin tran
declare @date char(6), -- 当前6位日期号 070718
@seq int-- 单据的序列号
select @date = convert(varchar(6),getdate(),12)
if not exists(select * from HEAD_NUMINFO
where head_category = @category and head_date = @date)
begin
set @seq = 1
insert into HEAD_NUMINFO values(@category,@date,@seq)
end
else
begin
update HEAD_NUMINFO set HEAD_SEQ = HEAD_SEQ + 1
where HEAD_CATEGORY = @category and HEAD_DATE = @date
select @seq = HEAD_SEQ from HEAD_NUMINFO
where HEAD_CATEGORY = @category and HEAD_DATE = @date
end
set @strHeadNumber = @category + @date + right( '0000 ' + cast(@seq as varchar(5)),5)
commit tran
-- 调用
declare @num char(14) -- 获取生成的单据号
execute makeHeadNumber 'ABC ',@num output
print @num
[解决办法]
用我这个吧，非常灵活的，任意表、任意字段，任意位置，任意前缀，任意条件。且可以缺号补号
ALTER PROCEDURE [dbo].[AutoNo]
@strTableName varchar(50),
@strColumnName varchar(50),
@strPreString varchar(50),
@iStartPos int,
@iLength int,
@strWhere varchar(800),
@bDiscontinuity bit
AS
declare @ReturnNo int
if len(@strWhere) > 0
set @strWhere = @strWhere + ' and '
if @bDiscontinuity = 0
begin
exec( 'select identity(int,1,1) xh,cast(substring( '+@strColumnName+ ', '+
@iStartPos+ ', '+@iLength+ ') as int) as CurCode into ##temp from (select '+@strColumnName+ ' from '+
@strTableName+ ' where '+@strwhere+@strColumnName+ ' like ' ' '+@strPreString+ '% ' ') as ls ')
set @ReturnNo = (select isnull(min(##temp.xh),0) as ReturnNo from ##temp where ##temp.xh <> ##temp.CurCode)
drop table ##temp
if @ReturnNo = 0 --说明没有检测到断号，此时要返回的是数据表中最大编号
begin
exec( 'select cast(substring( '+@strColumnName+ ', '+@iStartPos+ ', '+@iLength+
') as int) as MaxNo into ##temp1 from (select '+@strColumnName+ ' from '+@strTableName+
' where '+@strwhere+@strColumnName+ ' like ' ' '+@strPreString+ '% ' ') as ls ')
select isnull(max(##temp1.MaxNo),0)+1 as ReturnNo from ##temp1
drop table ##temp1
end
else
select @ReturnNo as ReturnNo
end
else
begin
exec( 'select cast(substring( '+@strColumnName+ ', '+@iStartPos+ ', '+@iLength+
') as int) as MaxNo into ##temp from (select '+@strColumnName+ ' from '+@strTableName+
' where '+@strwhere+@strColumnName+ ' like ' ' '+@strPreString+ '% ' ') as ls ')
exec( 'select isnull(max(##temp.MaxNo),0)+1 as ReturnNo from ##temp ')
drop table ##temp
end
[解决办法]
select replace(convert(char(10),getdate(),21), '- ', ' ')+right( '000 '+cast(ISNULL(max(cast(right( '070720002 ',3) as int)+1), '001 ') as varchar(3)),3)