如何更改计算列中的零系列

问题描述：

我正在使用下面的T-SQL创建一个计算列的表，其中给出了我在'BID(The Year)-0000'中的ID。问题是我想在年份发生变化时重置ID中的一系列零。例如，表中的最后一个ID是BID2017-0923，当年更改时我希望将该系列重置为例如'BID2018-0001'。如何更改计算列中的零系列

这是当前我正在使用的T-SQL。

CREATE TABLE Books 
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    IDCreateDate DATETIME NOT NULL DEFAULT GETDATE(), 
    BookID AS ('BID' + LTRIM(YEAR(IDCreateDate)) + '-' + RIGHT('0000' + LTRIM(ID), 4)), 
    ISBN VARCHAR(32), 
    BookName NVARCHAR(50), 
    AuthorName NVARCHAR(50), 
    BLanguage VARCHAR(50), 
    StaId int, 
    StuId int, 
    CatNo int 
);

UPDATE： 此外，我想ID山坳基础上，今年记住上次ID它has.For例如，在列的最后一个id为BID2017-0920当我改变的一年2010 ，它必须重置数字系列，例如BID2010-0001，但是当我将年份切换回2017年时，我不希望系列重置，而是希望它从BID2017-0921开始。

您可能需要在插入行时使用触发器来执行此操作。 –

创建表格时不可以这样做吗？ –

您的主键中不能有重复的值，这实际上就是您要求的。当你在某一年超过9,999个值时，你会怎么做？ –

答

编辑＃1：我根据最新OP的评论

更新我的解决方案

/* 
CREATE TABLE dbo.CustomSequence (
    Name VARCHAR(50) NOT NULL, 
    Prefix  VARCHAR(10) NOT NULL, 
    LastValue VARCHAR(50) NULL, -- All generated values will have following pattern: PrefixYYYY-SeqvNumber 
    LastYear SMALLINT NOT NULL, 
     CONSTRAINT PK_CustomSequence_Name_LastYear PRIMARY KEY (Name, LastYear), 
    LastNumber SMALLINT NULL 
); 
GO 
-- Config OrderSequence 2014 
INSERT dbo.CustomSequence (Name, Prefix, LastValue, LastYear, LastNumber) 
VALUES ('BookSequence', 'BID', NULL, 2014, 1) 
GO 
-- Config OrderSequence 2017 
INSERT dbo.CustomSequence (Name, Prefix, LastValue, LastYear, LastNumber) 
VALUES ('BookSequence', 'BID', NULL, 2017, 1) 
GO 
*/ 

-- Generating new seq 
    -- IN Parameters 
    DECLARE @CustomSequenceName VARCHAR(50) = 'BookSequence' 
    DECLARE @Year    SMALLINT = 2017 --YEAR(GETDATE()) 
    DECLARE @LenOfNumber  TINYINT = 4 
    -- End of IN Parameters 
    -- OUT Parameters 
    DECLARE @GeneratedValue  VARCHAR(50) 
    -- End of OUT Parameters 

    UPDATE s 
    SET  LastNumber  = IIF(LastValue IS NULL, LastNumber, LastNumber + 1) + IIF(LastNumber = REPLICATE('9', @LenOfNumber), 1/0, 0), 
      @GeneratedValue = LastValue = Prefix + LTRIM(@Year) + '-' + RIGHT(REPLICATE('0', @LenOfNumber) + LTRIM(IIF(LastValue IS NULL, LastNumber, LastNumber + 1)), @LenOfNumber) 
    FROM dbo.CustomSequence s 
    WHERE s.Name = @CustomSequenceName 
    AND  s.LastYear = @Year 
-- End of Generating new seq 

SELECT @GeneratedValue 
SELECT * FROM dbo.CustomSequence 
--> BID2017-0001, BID2017-0002, BID2017-0003, ...

注1：这与悲观并发控制（SQL Server数据库引擎的默认行为）

解决工作

注2：如果我们在下面的代码段达到10000

... IIF(LastNumber = 9999, 1/0, 0) ...

将引发异常

Msg 8134, Level 16, State 1, Line 30 
Divide by zero error encountered. 
The statement has been terminated.

注3：UPDATE语句可以被封装到与@CustomSequenceName VARCHAR(50)作为输入参数和@GeneratedValue VARCHAR(50) OUTPUT作为OUT [PUT]参数的存储过程。

注＃4：@LenOfNumber允许定制序列号的长度（默认值为4）

编辑＃2：

UPDATE语句可以用下面的INSERT + UPDATE语句的组合取代：

SET XACT_ABORT ON 
BEGIN TRAN 
    IF NOT EXISTS(SELECT * FROM dbo.CustomSequence s WITH(HOLDLOCK) WHERE s.Name = @CustomSequenceName AND s.LastYear = @Year) 
    BEGIN 
     INSERT dbo.CustomSequence (Name, Prefix, LastValue, LastYear, LastNumber) 
     VALUES (@CustomSequenceName, @Prefix, NULL, @Year, 1) 
    END 
    UPDATE s 
    SET  LastNumber  = IIF(LastValue IS NULL, LastNumber, LastNumber + 1) + IIF(LastNumber = REPLICATE('9', @LenOfNumber), 1/0, 0), 
      @GeneratedValue = LastValue = Prefix + LTRIM(@Year) + '-' + RIGHT(REPLICATE('0', @LenOfNumber) + LTRIM(IIF(LastValue IS NULL, LastNumber, LastNumber + 1)), @LenOfNumber) 
    FROM dbo.CustomSequence s WITH(HOLDLOCK) 
    WHERE s.Name = @CustomSequenceName 
    AND  s.LastYear = @Year 
COMMIT

如何更改计算列中的零系列

相关推荐