我的实现方法是,相关表建立一个sequence表,里边有IDENTITY列。每次需要的时候使用一个存储过程得到该表的唯一序号,前边加上日期,就可以了。 --------------------------- CREATE PROCEDURE PSEQUENCE @TableName varchar(20) AS Exec('INSERT INTO '+@TableName+' DEFAULT VALUES') Exec('SELECT @@IDENTITY AS '+@TableName) IF @@IDENTITY >=9999 BEGIN Exec('DELETE FROM '+@TableName) Exec('DBCC CHECKIDENT('+@TableName+',RESEED,-1)') END GO
create VIEW v_GetDate AS SELECT dt=replace(replace(replace(CONVERT(CHAR(16),GETDATE(),120),'-',''),' ',''),':','')+'-' GOdrop table tb --得到新编号的函数 ALTER FUNCTION f_NextBH() RETURNS char(16) AS BEGIN DECLARE @dt CHAR(13) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(1001+ISNULL(RIGHT(MAX(BH),3),0),3) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%') END GO--在表中应用函数 CREATE TABLE tb( BH char(16) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) --显示 SELECT * FROM tb
create proc TTTT as begin declare @New_id varchar(20) select top 1 @New_id= id from 表 order by id desc if convert(datetime,right(@new_id,8))=getdate() begin @new_id =right(@newid,9)+cast(convert(int,left(@new_id ,3))+1 as varchar) insert into 表 (id )values(@new_id ) end else begin @new_id =convert(varchar,getdate(),112)+'-001' insert into 表 (id )values(@new_id ) end end
每次都运行一增一条,先运行这个过程! 然后 select top 1 id from 表就得到这个新id了
create table #tmp(id char(14),item char(50)) insert #tmp select '20060418-001' ,'A' union all select '20060418-002' ,'B' select * from #tmpdeclare @nu as char(14) declare @max as char(3) set @nu='' Select top 1 @max=right(RTrim(id),3) from #tmp Where Left(id,8)=replace(Convert(Char(10),getdate(),20),'-','') Order By 1 desc --select @max if LTrim(RTrim(@max))='' Begin set @nu=replace(Convert(Char(10),getdate(),20),'-','') + '-001' End --select @nuElse Begin Set @max=@max+1 If @max Between 1 and 9 Begin set @max='00' + Cast(@max as char(1)) Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max End Else If @max Between 10 and 99 Begin set @max='0' + Cast(@max as char(2)) Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max End Else if @max Between 100 and 999 Begin Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max End
Endselect @nu
declare @nu as char(14) declare @max as char(3) set @nu='' set @max='' /*******************一定要加入這行,不要改日期后無結果******************/ Select top 1 @max=right(RTrim(id),3) from #tmp Where Left(id,8)=replace(Convert(Char(10),getdate(),20),'-','') Order By 1 desc --select @max if LTrim(RTrim(@max))='' Begin set @nu=replace(Convert(Char(10),getdate(),20),'-','') + '-001' End --select @nuElse Begin Set @max=@max+1 If @max Between 1 and 9 Begin set @max='00' + Cast(@max as char(1)) Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max End Else If @max Between 10 and 99 Begin set @max='0' + Cast(@max as char(2)) Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max End Else if @max Between 100 and 999 Begin Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max End
---------------------------
CREATE PROCEDURE PSEQUENCE
@TableName varchar(20)
AS
Exec('INSERT INTO '+@TableName+' DEFAULT VALUES')
Exec('SELECT @@IDENTITY AS '+@TableName)
IF @@IDENTITY >=9999
BEGIN
Exec('DELETE FROM '+@TableName)
Exec('DBCC CHECKIDENT('+@TableName+',RESEED,-1)')
END GO
AS
SELECT dt=replace(replace(replace(CONVERT(CHAR(16),GETDATE(),120),'-',''),' ',''),':','')+'-'
GOdrop table tb
--得到新编号的函数
ALTER FUNCTION f_NextBH()
RETURNS char(16)
AS
BEGIN
DECLARE @dt CHAR(13)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1001+ISNULL(RIGHT(MAX(BH),3),0),3)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(16) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
--显示
SELECT * FROM tb
as
begin
declare @New_id varchar(20)
select top 1 @New_id= id from 表
order by id desc
if convert(datetime,right(@new_id,8))=getdate()
begin
@new_id =right(@newid,9)+cast(convert(int,left(@new_id ,3))+1 as varchar)
insert into 表 (id )values(@new_id )
end
else
begin
@new_id =convert(varchar,getdate(),112)+'-001'
insert into 表 (id )values(@new_id )
end
end
然后 select top 1 id from 表就得到这个新id了
create table #tmp(id char(14),item char(50))
insert #tmp select '20060418-001' ,'A'
union all select '20060418-002' ,'B'
select * from #tmpdeclare @nu as char(14)
declare @max as char(3)
set @nu=''
Select top 1 @max=right(RTrim(id),3) from #tmp Where Left(id,8)=replace(Convert(Char(10),getdate(),20),'-','') Order By 1 desc
--select @max
if LTrim(RTrim(@max))=''
Begin
set @nu=replace(Convert(Char(10),getdate(),20),'-','') + '-001'
End
--select @nuElse
Begin
Set @max=@max+1
If @max Between 1 and 9
Begin
set @max='00' + Cast(@max as char(1))
Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max
End
Else
If @max Between 10 and 99
Begin
set @max='0' + Cast(@max as char(2))
Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max
End
Else
if @max Between 100 and 999
Begin
Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max
End
Endselect @nu
declare @max as char(3)
set @nu=''
set @max='' /*******************一定要加入這行,不要改日期后無結果******************/
Select top 1 @max=right(RTrim(id),3) from #tmp Where Left(id,8)=replace(Convert(Char(10),getdate(),20),'-','') Order By 1 desc
--select @max
if LTrim(RTrim(@max))=''
Begin
set @nu=replace(Convert(Char(10),getdate(),20),'-','') + '-001'
End
--select @nuElse
Begin
Set @max=@max+1
If @max Between 1 and 9
Begin
set @max='00' + Cast(@max as char(1))
Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max
End
Else
If @max Between 10 and 99
Begin
set @max='0' + Cast(@max as char(2))
Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max
End
Else
if @max Between 100 and 999
Begin
Set @nu=replace(Convert(Char(10),getdate(),20),'-','')+'-'+@max
End
Endselect @nu