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

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

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

回答：

/*
 * 测试表
*/
CREATE TABLE [dbo].[CustomIDTest] (
    [ID] [int] NOT NULL ,
    [Code] [char] (8) NOT 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(INT, SUBSTRING(@Code, 1, 4))
            /**//*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, -1, GetDate()))), dbo.GenCustomCode(DatePart(YY, DATEADD(YY, -1, GetDate())))
UNION
SELECT dbo.GenCustomID(DatePart(YY, GetDate())), dbo.GenCustomCode(DatePart(YY, GetDate()))
UNION
SELECT dbo.GenCustomID(DatePart(YY, DATEADD(YY, 1, GetDate()))), dbo.GenCustomCode(DatePart(YY, DATEADD(YY, 1, GetDate())))

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() 这样的非标量函数