Как изменить последовательность нулей в вычисляемом столбце

Я использую приведенный ниже T-SQL для создания таблицы с вычисляемым столбцом, который дает мне идентификаторы в 'BID(The Year)-0000'. Проблема в том, что я хотел бы сбросить серию нулей в идентификаторе при изменении года. Например, последний идентификатор в таблице равен 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
);

ОБНОВЛЕНИЕ: Кроме того, я хотел бы, чтобы столбец идентификатора запоминал свой последний идентификатор на основе года, который у него есть. Например, последний идентификатор в столбце равен BID2017-0920, когда я изменяю год на 2010, он чтобы сбросить числовой ряд, например BID2010-0001, но когда я переключаюсь обратно на 2017 год, я не хочу сбрасывать ряд, а хочу, чтобы он начинался с BID2017-0921.


database sql-server calculated-columns computed-field
person Elham Kohestani    schedule 02.08.2017    source источник
comment
Возможно, вам потребуется сделать это с помощью триггера при вставке строк.   -  person Gordon Linoff    schedule 02.08.2017
comment
Разве нельзя это сделать при создании таблицы?   -  person Elham Kohestani    schedule 02.08.2017
comment
У вас не может быть повторяющихся значений в вашем первичном ключе, о чем вы здесь и просите. И что вы собираетесь делать, когда превысите 9 999 значений за данный год?   -  person Sean Lange    schedule 02.08.2017
comment
В моем случае это не превысит 9999.   -  person Elham Kohestani    schedule 02.08.2017
comment
В сценарии, для которого я создаю таблицу, количество записей вряд ли достигает 1000.   -  person Elham Kohestani    schedule 02.08.2017
comment
Даже в этом случае вы запрашиваете повторяющиеся значения в столбце первичного ключа, что невозможно по определению. Вероятно, вам следует сделать это в триггере вставки и использовать row_number, который вы можете разделить по годам.   -  person Sean Lange    schedule 02.08.2017
comment
И я бы очень осторожно относился к созданию какой-то логики, которая работает сегодня, но сломается, если система будет использоваться больше, чем сегодня. Такое ограничение до 4 символов действительно хрупкое.   -  person Sean Lange    schedule 02.08.2017
comment
Можете ли вы опубликовать свой ответ.   -  person Elham Kohestani    schedule 03.08.2017

Ответы (1)


arrow_upward
0
arrow_downward

Изменить №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 Database Engine по умолчанию).

Примечание № 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
person Bogdan Sahlean    schedule 02.08.2017