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
* 测试表
*/
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
-- 自定义年份内自增
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
----------- --------
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() 这样的非标量函数