如何更改计算列中的零序列 [英] How to change the series of zeroes in a computed column
问题描述
我正在使用下面的T-SQL创建具有计算列的表,该表为我提供了'BID(The Year)-0000'
中的ID.问题是我想在更改年份时重置ID中的零序列.例如,当更改年份时表中的最后一个ID是BID2017-0923
,我希望重置序列,例如'BID2018-0001'
.
I am using the below T-SQL to create a table with a computed column which gives me IDs in 'BID(The Year)-0000'
. The issue is that I would like to reset the series of zeros in the ID when the year is changed.For example, the last ID in the table is BID2017-0923
when the year is changed I want the series to be reset for example 'BID2018-0001'
.
这是我当前正在使用的T-SQL.
Here is the T-SQL which currently I am using.
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
);
更新:
此外,我希望ID col记住它基于年份的最后一个ID,例如,当我将年份更改为2010时,列中的最后一个ID是BID2017-0920
,例如它必须重置数字系列BID2010-0001
,但是当我将年份切换回2017年时,我不想重置该系列,而是希望它从BID2017-0921
开始.
UPDATE:
Furthermore, I would like the ID col to remembers its last id based on the year it has.For example, the last id in the column is BID2017-0920
when I change the year to 2010, it has to reset the number series for example BID2010-0001
but when I switch back the year to 2017, I don't want the series to be reset rather I want it to start from BID2017-0921
.
推荐答案
编辑#1:我已根据最新OP的评论更新了解决方案
Edit#1: I've updated my solution according to latest OP's comments
/*
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数据库引擎的默认行为)
Note#1: This solution work with pessimistic concurrency control (default behavior of SQL Server Database Engine)
注意#2:如果我们达到以下代码段的10000
Note#2: If we are reaching 10000 following piece of code
... 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]参数.
Note#3: UPDATE statement could be encapsulated into a stored procedure with @CustomSequenceName VARCHAR(50)
as input parameter and @GeneratedValue VARCHAR(50) OUTPUT
as OUT[PUT] parameter.
注意#4:@LenOfNumber允许自定义序列号的长度(默认为4)
Note#4: @LenOfNumber allows to customize length of sequential number (default is 4)
编辑#2:
UPDATE语句可以替换为以下INSERT + UPDATE语句组合:
UPDATE statement could be replaced with following combination of INSERT + UPDATE statements:
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
这篇关于如何更改计算列中的零序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!