SQL 中按年份生成8位自增编码/标识
来源:百度文库 编辑:神马文学网 时间:2024/04/27 21:03:35
我想在数据库中的某列依次存储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() 这样的非标量函数)
http://community.csdn.net/Expert/TopicView3.asp?id=5696192
* 测试表
*/
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() 这样的非标量函数)
SQL 中按年份生成8位自增编码/标识
msicc-漫谈SQL Server中的标识列(一)
sql中round函数
T-SQL 生成 两个新的真正的公历年历
文献标识码
editplus中SQL的配置
SQL中判断是否存在..........
自言字语-在线生成logo,印章工具
VC中生成汇编文件
sql中为表加约束的sql语句
T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
sql server 中易混淆的数据类型
SQL中与IP相关的常见问题
SQL Server 中易混淆的数据类型
JSP中SQL数据库编程技术
JSP中SQL数据库编程技术
SQL中各数据类型的长度、精度
在MySQL中执行SQL文件
sql server 中易混淆的数据类型
SQL Server中 CONVERT 将日期格式化
检测mysql中sql语句的效率
Excel中如何调用SQL数据
SQL Server2000中地日期格式