不太明白你存储数据的过程,不过用函数应该可以, 比如以下函数create function GetIndex() return varchar(8) as begin select case when ... then max(index) + 1 end ... from ... where ... end然后插入的时候insert into ... values(..., GetIndex(), ...)
1. 没有内置 API 支持此功能2. 通过自定义函数可以实现->/* * 测试表 */ 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
return varchar(8)
as
begin
select case when ... then max(index) + 1 end ... from ... where ...
end然后插入的时候insert into ... values(..., GetIndex(), ...)
没有内置 API 支持此功能2.
通过自定义函数可以实现->/*
* 测试表
*/
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