自动编号（用当前时间生成编号）

最新推荐文章于 2018-11-08 13:14:17 发布

原创最新推荐文章于 2018-11-08 13:14:17 发布 · 1.3k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#insert #go #null #delete #table #function

SQL Server 2000 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种使用SQL生成带有日期前缀的自增编号的方法，并提供了两种函数实现：一种是简单地生成下一个可用编号；另一种则能处理编号缺失的情况，确保编号连续。这两种方法对于需要特定编号格式的应用场景非常有用。

我们经常设置表的ＩＤ的时候都是用IDENTITY来产生编号，或者用Default(newID()),产生出来的编号是一串字母的组合，这是最经常用的办法。
下面的代码是用时间的年和月加上一串自定义的编号组成新的编号，并提供了补号的方法，也就是说当用户表中某条信息删除的时候，我们要将个编号补上，比哪开始编号为1001，1002，1003，当我们删除1002的时候，再插入新的一行的时候，编号会变成1001，1003，1004，可是这也许不是我们想要的，我们想要的是在新插入一行时编号是1001，1002，1003，下面提供了补号与不补号两个函数的测试。当然这种东西也很少碰到，但有时候还是挺有用的。

--建表语句
CREATE TABLE [MyTable] (
    [MyId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [MyName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

--得到新的编号（不考虑补号）
create function f_NextID(@myDate datetime)
Returns char(12)
as
begin
    Declare @temp char(6)
     Declare @ret char(12)
    set @temp=Substring(Convert(char,@myDate),9,2)+Substring(Convert(char,@myDate),1,2)+Substring(Convert(char,@myDate),4,2)
    if(@temp in (SELECT distinct myid=left(myid,6) FROM mytable WITH(XLOCK,PAGLOCK)))
            Select @ret=@temp+right(1000001+Isnull(Max(Right(MyID,6)),0),6) from Mytable with(xlock,paglock) where @temp=left(MyID,6)
    else
        Select @ret=@temp+'000001' from Mytable with(xlock,paglock)
    return @ret
end
GO

drop table MyTable
--建表语句
CREATE TABLE [MyTable] (
    [MyId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [MyName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

--得到新的编号（融合了补号处理）
create FUNCTION f_NextNewID(@myDate datetime)
RETURNS char(12)
AS
BEGIN
    Declare @temp char(6)
     Declare @ret char(12)
    set @temp=Substring(Convert(char,@myDate),9,2)+Substring(Convert(char,@myDate),1,2)+Substring(Convert(char,@myDate),4,2)
    DECLARE @r char(12)

    SELECT @r=@temp+RIGHT(1000001+MIN(MyId),6)
    FROM(
        SELECT MyId=RIGHT(MyId,6) FROM MyTable WITH(XLOCK,PAGLOCK)
        UNION ALL SELECT 0
    )a
  WHERE NOT EXISTS(
        SELECT * FROM MyTable  WITH(XLOCK,PAGLOCK)
        WHERE MyId=@temp+RIGHT(1000001+a.myID,6))
    RETURN(@r)
END
GO

--测试语句(可改变时间测试一下）
insert MyTable values (dbo.f_NextNewID(GetDate()),'one')
insert MyTable values (dbo.f_NextNewID(GetDate()),'two')
insert MyTable values (dbo.f_NextNewID(GetDate()),'three')
insert MyTable values (dbo.f_NextNewID(GetDate()),'four')
insert MyTable values (dbo.f_NextNewID(GetDate()),'five')
insert MyTable values (dbo.f_NextNewID(GetDate()),'six')
Select * from MyTable order by MyId
GO
delete MyTable where myId='060629000002'
delete MyTable where myId='060629000005'
Select * from MyTable order by MyId
Go
insert MyTable values (dbo.f_NextNewID(GetDate()),'nexttwo')
insert MyTable values (dbo.f_NextNewID(GetDate()),'nextfive')
Select * from MyTable order by MyId
GO