--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。 --得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(8) AS BEGIN RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK)) END GO--在表中应用函数 CREATE TABLE tb( BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 BEGIN TRAN INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14) COMMIT TRAN--显示结果 SELECT * FROM tb /*--结果 BH col ---------------- ----------- BH000001 1 BH000002 2 BH000003 4 BH000004 14 --*/
if OBJECT_ID('fun_test')is not null drop function fun_test go create function fun_test (@id int,@date datetime) returns varchar as begin return 'ADS/WT-'+case when day(@date)>=20 then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2) else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end end go
---->>>TravyLee生成测试数据 if OBJECT_ID('fun_test')is not null drop function fun_test go create function fun_test (@id int,@date datetime) returns varchar(20) as begin return 'ADS/WT-'+case when day(@date)>=20 then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2) else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end end go if OBJECT_ID('test') is not null drop table test go create table test( id int identity, dates date ) go insert test(dates) select '2012-05-19' union all select '2012-05-20' union all select '2012-05-21' union all select '2012-05-22' select dbo.Fun_test(id,dates) as BH,dates from test /* BH dates ---------------------------------- ADS/WT-0501 2012-05-19 ADS/WT-0602 2012-05-20 ADS/WT-0603 2012-05-21 ADS/WT-0604 2012-05-22 */
有种比较特殊的情况就是2012.12.21到2013.01.20ADS/WT-2013-01XX
---->>>TravyLee生成测试数据 if OBJECT_ID('fun_test')is not null drop function fun_test go create function fun_test (@id int,@date datetime) returns varchar(20) as begin return 'ADS/WT-'+case when day(@date)>=20 then ltrim(year(dateadd(mm,1,@date)))+'-'+right('00'+ltrim(month(dateadd(mm,1,@date))),2)+right('00'+ltrim(@id),2) else ltrim(year(@date))+'-'+right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end end go if OBJECT_ID('test') is not null drop table test go create table test( id int identity, dates date ) go insert test(dates) select '2012-05-19' union all select '2012-05-20' union all select '2012-05-21' union all select '2012-05-22' union all select '2012-12-20' union all select '2012-12-21' select dbo.Fun_test(id,dates) as BH,dates from test /* BH dates ADS/WT-2012-0501 2012-05-19 ADS/WT-2012-0602 2012-05-20 ADS/WT-2012-0603 2012-05-21 ADS/WT-2012-0604 2012-05-22 ADS/WT-2013-0105 2012-12-20 ADS/WT-2013-0106 2012-12-21 */
declare @begin_date int declare @day int declare @code varchar(32) declare @year varchar(4) declare @month int set @code='ADS/WT-' set @year =CONVERT(varchar(4),year(getdate())) set @month =MONTH(getdate()) set @day =DAY(getdate()) if (@day>=21) begin set @month=@month +1 if(@month=13) set @month=1 end set @code=@code+@year+'-'+RIGHT(convert(varchar(10),(@month+100)),2)+CONVERT(varchar(2),day(getdate()))print @code ---ADS/WT-2012-0628
drop table Ago create table A (AA varchar(20), date char(10) )declare @date char(10) declare @date1 datetime,@date2 datetime declare @bignum varchar(20) declare @bignum1 varchar(20) set @date='2012-02-21' if day(@date)>20 begin set @date1=left(@date,8)+'20' set @date2=dateadd(month,1,@date1) end else begin set @date2=left(@date,8)+'20' set @date1=dateadd(month,-1,@date2) end print @date1 print @date2 select @bignum=max(AA) from A where @date1<=date and @date2>=date print @bignum if @bignum is null begin select 'ADS/WT-'+left(convert(char(10),@date2,23),7)+'01' end else begin select @bignum+1 end
create function f_get_code() returns varchar(32) as begin declare @begin_date int declare @day int declare @code varchar(32) declare @year varchar(4) declare @month int set @code='ADS/WT-' set @year =CONVERT(varchar(4),year(getdate())) set @month =MONTH(getdate()) set @day =DAY(getdate()) if (@day>=21) begin set @month=@month +1 if(@month=13) set @month=1 end set @code=@code+@year+'-'+RIGHT(convert(varchar(10),(@month+100)),2)+CONVERT(varchar(2),day(getdate())) return @code endgo create table test( id int identity(1,1), code varchar(32) ) go insert test select dbo.f_get_code() as code go select * from test /* id code 1 ADS/WT-2012-0628 */
--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/
if OBJECT_ID('fun_test')is not null
drop function fun_test
go
create function fun_test
(@id int,@date datetime)
returns varchar
as
begin
return
'ADS/WT-'+case when day(@date)>=20
then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2)
else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end
end
go
---->>>TravyLee生成测试数据
if OBJECT_ID('fun_test')is not null
drop function fun_test
go
create function fun_test
(@id int,@date datetime)
returns varchar(20)
as
begin
return
'ADS/WT-'+case when day(@date)>=20
then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2)
else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end
end
go
if OBJECT_ID('test') is not null
drop table test
go
create table test(
id int identity,
dates date
)
go
insert test(dates)
select '2012-05-19' union all
select '2012-05-20' union all
select '2012-05-21' union all
select '2012-05-22'
select dbo.Fun_test(id,dates) as BH,dates from test
/*
BH dates
----------------------------------
ADS/WT-0501 2012-05-19
ADS/WT-0602 2012-05-20
ADS/WT-0603 2012-05-21
ADS/WT-0604 2012-05-22
*/
---->>>TravyLee生成测试数据
if OBJECT_ID('fun_test')is not null
drop function fun_test
go
create function fun_test
(@id int,@date datetime)
returns varchar(20)
as
begin
return
'ADS/WT-'+case when day(@date)>=20
then ltrim(year(dateadd(mm,1,@date)))+'-'+right('00'+ltrim(month(dateadd(mm,1,@date))),2)+right('00'+ltrim(@id),2)
else ltrim(year(@date))+'-'+right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end
end
go
if OBJECT_ID('test') is not null
drop table test
go
create table test(
id int identity,
dates date
)
go
insert test(dates)
select '2012-05-19' union all
select '2012-05-20' union all
select '2012-05-21' union all
select '2012-05-22' union all
select '2012-12-20' union all
select '2012-12-21' select dbo.Fun_test(id,dates) as BH,dates from test
/*
BH dates
ADS/WT-2012-0501 2012-05-19
ADS/WT-2012-0602 2012-05-20
ADS/WT-2012-0603 2012-05-21
ADS/WT-2012-0604 2012-05-22
ADS/WT-2013-0105 2012-12-20
ADS/WT-2013-0106 2012-12-21
*/
declare @day int
declare @code varchar(32)
declare @year varchar(4)
declare @month int
set @code='ADS/WT-'
set @year =CONVERT(varchar(4),year(getdate()))
set @month =MONTH(getdate())
set @day =DAY(getdate())
if (@day>=21)
begin
set @month=@month +1
if(@month=13)
set @month=1
end
set @code=@code+@year+'-'+RIGHT(convert(varchar(10),(@month+100)),2)+CONVERT(varchar(2),day(getdate()))print @code
---ADS/WT-2012-0628
create table A
(AA varchar(20),
date char(10)
)declare @date char(10)
declare @date1 datetime,@date2 datetime
declare @bignum varchar(20)
declare @bignum1 varchar(20)
set @date='2012-02-21'
if day(@date)>20
begin
set @date1=left(@date,8)+'20'
set @date2=dateadd(month,1,@date1)
end
else
begin
set @date2=left(@date,8)+'20'
set @date1=dateadd(month,-1,@date2)
end
print @date1
print @date2
select @bignum=max(AA) from A where @date1<=date and @date2>=date
print @bignum
if @bignum is null
begin
select 'ADS/WT-'+left(convert(char(10),@date2,23),7)+'01'
end
else
begin
select @bignum+1
end
returns varchar(32)
as
begin declare @begin_date int
declare @day int
declare @code varchar(32)
declare @year varchar(4)
declare @month int
set @code='ADS/WT-'
set @year =CONVERT(varchar(4),year(getdate()))
set @month =MONTH(getdate())
set @day =DAY(getdate())
if (@day>=21)
begin
set @month=@month +1
if(@month=13)
set @month=1
end
set @code=@code+@year+'-'+RIGHT(convert(varchar(10),(@month+100)),2)+CONVERT(varchar(2),day(getdate()))
return @code
endgo
create table test(
id int identity(1,1),
code varchar(32)
)
go
insert test select dbo.f_get_code() as code
go
select * from test
/*
id code
1 ADS/WT-2012-0628
*/