我们经常设置表的ID的时候都是用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
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