/**
getno----函数名
orderno---- 字段名
tblorder---表名字
**/
CREATE FUNCTION [dbo].[getno]()
RETURNS char(10)
AS
BEGIN
declare @id varchar(50),@i int
select top 1 @id=rtrim(orderno) from dbo.tblorder where left(orderno,6)= convert(varchar(6),GETDATE(),12) order by orderno desc
if @@rowcount=0
return convert(varchar(6),GETDATE(),12)+'-'+'001'
set @i= rtrim(right(@id,3)) +1
set @id=rtrim(@i)
return convert(varchar(6),GETDATE(),12)+'-'+right( '000'+@id ,3 )
getno----函数名
orderno---- 字段名
tblorder---表名字
**/
CREATE FUNCTION [dbo].[getno]()
RETURNS char(10)
AS
BEGIN
declare @id varchar(50),@i int
select top 1 @id=rtrim(orderno) from dbo.tblorder where left(orderno,6)= convert(varchar(6),GETDATE(),12) order by orderno desc
if @@rowcount=0
return convert(varchar(6),GETDATE(),12)+'-'+'001'
set @i= rtrim(right(@id,3)) +1
set @id=rtrim(@i)
return convert(varchar(6),GETDATE(),12)+'-'+right( '000'+@id ,3 )
END
调用方法:表字段中添加默认值:([dbo].[getno]())
实现效果:
180420-001
180420-002
180420-003
180420-004
180421-001
180421-002