一般都是
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))))上面两个方法都不行.高手来帮忙啊!
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))
这样的话SID一直在长啊.
如果是第二天那不是变成SR070718005...但我第二天也要从是1开始的
DBCC Checkident(表名,Reseed,0) --每天重新从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 行)
*/
我要实现的是OR070717001
OR是固定不变的,070717是根据取当天日期除去了开头的20,001是根据当天的订单数据来判断的.001表示第一个订单第二的001又重新开始
Create table t(ID int identity(1,1),
OrderID as ('SR'+convert(varchar,year(getdate()))+convert(varchar,month(getdate()))+
convert(varchar,day(getdate()))+Replicate('0',3-Len(ID)) +rtrim(ID) ), ch char(1))--建立每天执行的Job
DBCC Checkident(t,Reseed,0)--即可实现LZ的要求
----------------------------------
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
参考:-- 创建表
CREATE TABLE HEAD_NUMINFO(
HEAD_CATEGORY CHAR(3), -- 单据类型前缀
HEAD_DATE CHAR(6), -- 生成的6位单据日期号
HEAD_SEQ INT -- 单据序列号
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
1:建立一个表a,该表有一个自增长字段fa。从1开始,增量是1。
2:做个定时器,每天凌晨00:00:00的时候将该表的字增量改为1,增量为1;就是重新建立该表的自增长说明。
3:每当要保存一份订单的时候,先在a中插入一条记录。获得该记录fa字段的最新值(在一个事务中这样是比较容易的)
4:取日期,组合好你需要的编码,写到订单数据中。
不知道这样能否满足你的需求?有点麻烦,不过也不会造成多大的性能损失。用sql组织,在一个事务中完成。
这个好,很受用,这句收着了.多谢..
超出了存储过程、函数、触发器或视图的最大嵌套层数最大层数为 32
怎么处理
@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
======================================================经测试``
我要形成一个文件:文件的名称的命名规则是:****+MM(月)+DD(日)+流水号码 其中流水号码为01--79的任意两码.那么每天的流水号码不能重复.
在文件内容中还有一个栏位的数据是这样的组成的:****+YY+MM+流水号码 其中流水号码为任意三码组成.是否需要建立一个表来维护这些数据呢?还是各位有更好的办法?谢谢.