代码改变世界

SQL 中按年份生成8位自增编码/标识

2007-08-08 00:29  晓风残月  阅读(...)  评论(...编辑  收藏

 

看到 CSDN 中有网友提出这个需求,肯定有大虾实现了,只是没有搜索到,自己就实现了一个

我想在数据库中的某列依次存储20070001,20070002等,而到了2008年,又变成了2008001,2008002,这可以在数据库中设置成默认值吗?如果可以怎么设?

http://community.csdn.net/Expert/TopicView3.asp?id=5696192

/*
 * 测试表
*/

CREATE TABLE [dbo].[CustomIDTest] (
    
[ID] [int] NOT NULL ,
    
[Code] [char] (8NOT NULL 
)

/*
 * 功能:按 YYYYxxxx 格式生成目标年份的最大编码,数据类型为 CHAR(8)
 * 说明:特定表范围内有效
 *       未处理溢出情况(当表中某年值已达到YYYY9999)
*/

CREATE FUNCTION dbo.GenCustomCode(
    
@Year INT
)
RETURNS CHAR(8)
AS
    
BEGIN

        
DECLARE 
            
@Code CHAR(8),
            
@MinCodeInYear CHAR(8),
            
@MaxCodeInYear CHAR(8)

        
SELECT @MinCodeInYear = CONVERT(CHAR(4), @Year+ '0001'@MaxCodeInYear = CONVERT(CHAR(4), @Year+ '9999'
        
        
SELECT @Code = MAX(Code) FROM CustomIDTest WHERE Code >= @MinCodeInYear AND Code <= @MaxCodeInYear
        
IF @Code IS NOT NULL AND @Year = CONVERT(INTSUBSTRING(@Code14))
            
/*IF @Code = @MaxCodeInYear 溢出处理*/            
            
SET @Code = CONVERT(INT@Code+ 1
        
ELSE
             
SET @Code = @MinCodeInYear
    
        
RETURN @Code

    
END

/*
 * 功能:按 YYYYxxxx 格式生成目标年份的最大ID,数据类型为 INT
 * 说明:特定表范围内有效
 *       未处理溢出情况(当表中某年值已达到YYYY9999)
*/

CREATE FUNCTION dbo.GenCustomID(
    
@Year INT
)
RETURNS INT
AS
    
BEGIN

        
DECLARE 
            
@ID INT,
            
@MinIDInYear INT,
            
@MaxIDInYear INT

        
SELECT @MinIDInYear = @Year*10000 + 1@MaxIDInYear = @Year*10000 + 9999
        
        
SELECT @ID = MAX(ID) FROM CustomIDTest WHERE ID >= @MinIDInYear AND ID <= @MaxIDInYear
        
IF @ID IS NOT NULL AND @Year = @ID/10000
            
/*IF @ID = @MaxIDInYear 溢出处理*/            
            
SET @ID = @ID + 1
        
ELSE
             
SET @ID = @MinIDInYear
    
        
RETURN @ID

    
END



-- 测试
--
 自定义年份内自增
INSERT INTO CustomIDTest([ID][Code])
SELECT dbo.GenCustomID(DatePart(YY, DATEADD(YY, -1GetDate()))), dbo.GenCustomCode(DatePart(YY, DATEADD(YY, -1GetDate())))
UNION
SELECT dbo.GenCustomID(DatePart(YY, GetDate())), dbo.GenCustomCode(DatePart(YY, GetDate()))
UNION
SELECT dbo.GenCustomID(DatePart(YY, DATEADD(YY, 1GetDate()))), dbo.GenCustomCode(DatePart(YY, DATEADD(YY, 1GetDate())))

SELECT * FROM CustomIDTest

结果
ID          Code     
----------- -------- 
20060001    20060001
20060002    20060002
20060003    20060003
20060004    20060004
20070001    20070001
20070002    20070002
20070003    20070003
20070004    20070004
20080001    20080001
20080002    20080002
20080003    20080003
20080004    20080004

说明:
1、对于此示例,一年内最大增量只有,9999,需要考虑溢出如何处理,此版本未处理
2、因此,根据实际需求,我们可以选择以“天”为编码前缀,并且扩大自增部分长度,避免溢出,
3、当然此时得考虑,使用 INT 型长度是否足够
4、此版本自定义函数,使用传入年份参数
5、若欲始终使用当前系统日期,而不显示指定年份,则需要建立一视图来获取当前日期(因SQLServer中标量函数,不能使用 GetDate() 这样的非标量函数)