Найти последнее значение в «повторяющейся» последовательности с помощью хранимой процедуры?

Asked
Viewd237

0

Предположим, у меня есть набор буквенных идентификаторов заданной длины, например всегда пять букв, и они назначаются таким образом, что они всегда увеличиваются последовательно (GGGGZ -> GGGHA и т. д.). Теперь, если я доберусь до ZZZZZ, так как длина фиксированная, я должен "перекатиться" на AAAAA. У меня может быть непрерывный блок от ZZZAA до AAAAM. Я хочу написать sproc, который даст мне "следующий" идентификатор, в данном случае AAAAN.

Если бы у меня не было этой проблемы "переноса", я бы, конечно, просто ЗАКАЗЫВАЛ ПО УДАЛЕНИЮ и получил лучший результат. Но сейчас я немного растерялся - и мне совершенно не помогает то, что SQL - не самый сильный язык для меня.

Если у меня есть , я могу переместить это в свой вызывающий код C #, но sproc будет лучше.

ETA: я бы не хотел изменять схему (новый столбец или новая таблица); Я бы предпочел просто «разобраться». Я мог бы даже предпочесть использовать грубую силу (например, начать с наименьшего значения и увеличивать, пока не найду «дыру»), даже если это может оказаться дорогостоящим. Если у вас есть ответ, который не изменяет схему, это было бы лучшим решением для моих нужд.

7 ответов

0

Я думаю, что самым эффективным решением для моих нужд является добавление столбца идентификации. Единственное, что я могу гарантировать, это то, что порядок будет таким, что записи, которые должны быть «первыми», будут добавлены первыми - я никогда не буду добавлять записи с идентификатором BBBB, а затем вернусь и добавлю BBBA позже. Если бы у меня не было этого ограничения, очевидно, что это не сработало бы, но в существующем виде я могу просто упорядочить по столбцу идентичности и получить желаемую сортировку.

Я буду думать о других предложениях - может быть, если они «щелкнут» в моей голове, они будут выглядеть как лучший вариант.

0

Проблема заключается в том, что вы не можете точно определить по данным, где находится «последняя» запись, если нет более подробной информации о том, как удаляются старые записи.

Если я правильно понимаю, вы завершаете цикл в конце последовательности, что означает, что вы должны удалить некоторые из своих старых данных, чтобы освободить место. Однако, если данные не будут удалены совершенно единообразно, вы получите фрагменты, как показано ниже:

 ABCD   HIJKL NOPQRS   WXYZ
 

Вы заметите, что нет очевидного следующего значения ... D может быть последним созданным значением, но это также может быть L или S.

В лучшем случае вы можете искать первый или последний отсутствующий элемент (используйте хранимую процедуру для проверки x + 1, как если бы вы искали отсутствующий элемент в целочисленной последовательности), но она не будет предоставлять никаких специальных результат для прокручиваемых списков.

  • Последовательность не всегда начинается с начала. Иногда он начинается с BBBB, а иногда - с YYYY, поэтому в начале алфавита уже есть «место»

    Coderer09 апреля 2009, 13:09
0

Поскольку мне не хочется писать код для увеличения букв, я бы создал таблицу всех допустимых идентификаторов (от AAAAAA до ZZZZZZ) с целым числом от 1 до X для этих идентификаторов. Тогда вы можете использовать следующее:

 SELECT @max_id = MAX(id) FROM Possible_Silly_IDs

SELECT
    COALESCE(MAX(PSI2.silly_id), 'AAAAAA')
FROM
    My_Table T1
INNER JOIN Possible_Silly_IDs PSI1 ON
    PSI1.silly_id = T1.silly_id
INNER JOIN Possible_Silly_IDs PSI2 ON
    PSI2.id = CASE WHEN PSI1.id = @max_id THEN 1 ELSE PSI1.id + 1 END
LEFT OUTER JOIN My_Table T2 ON
    T2.silly_id = PSI2.silly_id
WHERE
    T2.silly_id IS NULL
 

COALESCE присутствует, если таблица пуста. Чтобы быть по-настоящему надежным, вы должны вычислить 'AAAAAA' (SELECT @min_silly_id = silly_id WHERE id = 1) в случае изменения вашего алгоритма "нумерации".

Если вы действительно хотите сделать все правильно, вы должны переделать дизайн базы данных, как было предложено.

0

Чтобы вернуть следующий ID для данного ID (с наведением курсора), используйте:

 SELECT  COALESCE
        (
        (
        SELECT  TOP 1 id
        FROM    mytable
        WHERE   id > @id
        ORDER BY
                id
        ),
        (
        SELECT  TOP 1 id
        FROM    mytable
        ORDER BY
                id
        )
        ) AS nextid
 

Этот запрос ищет ID рядом с заданным. Если такого ID нет, возвращается первый ID.

Вот результаты:

 WITH mytable AS
        (
        SELECT  'AAA' AS id
        UNION ALL
        SELECT  'BBB' AS id
        UNION ALL
        SELECT  'CCC' AS id
        UNION ALL
        SELECT  'DDD' AS id
        UNION ALL
        SELECT  'EEE' AS id
        )
SELECT  mo.id,
        COALESCE
        (
        (
        SELECT  TOP 1 id
        FROM    mytable mi
        WHERE   mi.id > mo.id
        ORDER BY
                id
        ),
        (
        SELECT  TOP 1 id
        FROM    mytable mi
        ORDER BY
                id
        )
        ) AS nextid
FROM    mytable mo

id      nextid
-----   ------
AAA     BBB
BBB     CCC
CCC     DDD
DDD     EEE
EEE     AAA
 

, т. е. он возвращает BBB для AAA, CCC для BBB и т. д. и, наконец, AAA для EEE, который является последним в таблице.

0

Думаю, я бы попытался сохранить последовательность как целое число, а затем преобразовать ее в строку. Или же сохраните параллельный целочисленный столбец, который увеличивается одновременно с альфа-значением. В любом случае можно выполнить сортировку по целочисленному столбцу.

1

Вот код, который, я думаю, даст вам значение Next. Я создал 3 функции. Таблица - это просто моя имитация table.column с вашими альфа-идентификаторами (я использовал MyTable.AlphaID). Я предполагаю, что это так, как вы подразумевали, и есть один непрерывный блок пятисимвольных буквенных строк в верхнем регистре (AlphaID):

 IF OBJECT_ID('dbo.MyTable','U') IS NOT NULL
    DROP TABLE dbo.MyTable
GO
CREATE TABLE dbo.MyTable (AlphaID char(5) PRIMARY KEY)
GO
-- Play with different population scenarios for testing
INSERT dbo.MyTable VALUES ('ZZZZY')
INSERT dbo.MyTable VALUES ('ZZZZZ')
INSERT dbo.MyTable VALUES ('AAAAA')
INSERT dbo.MyTable VALUES ('AAAAB')
GO
IF OBJECT_ID('dbo.ConvertAlphaIDToInt','FN') IS NOT NULL
    DROP FUNCTION dbo.ConvertAlphaIDToInt
GO
CREATE FUNCTION dbo.ConvertAlphaIDToInt (@AlphaID char(5))
RETURNS int
AS
BEGIN
RETURN 1+ ASCII(SUBSTRING(@AlphaID,5,1))-65
              + ((ASCII(SUBSTRING(@AlphaID,4,1))-65) * 26)
              + ((ASCII(SUBSTRING(@AlphaID,3,1))-65) * POWER(26,2))
              + ((ASCII(SUBSTRING(@AlphaID,2,1))-65) * POWER(26,3))
              + ((ASCII(SUBSTRING(@AlphaID,1,1))-65) * POWER(26,4))
END
GO 

IF OBJECT_ID('dbo.ConvertIntToAlphaID','FN') IS NOT NULL
    DROP FUNCTION dbo.ConvertIntToAlphaID
GO
CREATE FUNCTION dbo.ConvertIntToAlphaID (@ID int)
RETURNS char(5)
AS
BEGIN
RETURN CHAR((@ID-1) / POWER(26,4) + 65)
      + CHAR ((@ID-1) % POWER(26,4) / POWER(26,3) + 65)
      + CHAR ((@ID-1) % POWER(26,3) / POWER(26,2) + 65)
      + CHAR ((@ID-1) % POWER(26,2) / 26 + 65)
      + CHAR ((@ID-1) % 26 + 65)

END
GO 
IF OBJECT_ID('dbo.GetNextAlphaID','FN') IS NOT NULL
    DROP FUNCTION dbo.GetNextAlphaID
GO
CREATE FUNCTION dbo.GetNextAlphaID ()
RETURNS char(5)
AS
BEGIN
    DECLARE @MaxID char(5), @ReturnVal char(5)
    SELECT @MaxID = MAX(AlphaID) FROM dbo.MyTable
    IF @MaxID < 'ZZZZZ'
        RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
    IF @MaxID IS NULL
        RETURN 'AAAAA'
    SELECT @MaxID = MAX(AlphaID) 
    FROM dbo.MyTable 
    WHERE AlphaID < dbo.ConvertIntToAlphaID((SELECT COUNT(*) FROM dbo.MyTable))
    IF @MaxID IS NULL
        RETURN 'AAAAA'
    RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
END
GO

SELECT * FROM dbo.MyTable ORDER BY dbo.ConvertAlphaIDToInt(AlphaID)
GO
SELECT  dbo.GetNextAlphaID () AS 'NextAlphaID'
 

Кстати, если вы не хотите предполагать непрерывность, вы можете сделать так, как вы предложили, и (если есть строка «ZZZZZ») использовать первый пробел в последовательности. Замените последнюю функцию на эту:

 IF OBJECT_ID('dbo.GetNextAlphaID_2','FN') IS NOT NULL
    DROP FUNCTION dbo.GetNextAlphaID_2
GO
CREATE FUNCTION dbo.GetNextAlphaID_2 ()
RETURNS char(5)
AS
BEGIN
    DECLARE @MaxID char(5), @ReturnVal char(5)
    SELECT @MaxID = MAX(AlphaID) FROM dbo.MyTable
    IF @MaxID < 'ZZZZZ'
        RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
    IF @MaxID IS NULL
        RETURN 'AAAAA'
    SELECT TOP 1 @MaxID=M1.AlphaID
    FROM dbo.Mytable M1
    WHERE NOT EXISTS (SELECT 1 FROM dbo.MyTable M2 
                      WHERE AlphaID = dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(M1.AlphaID) + 1 )
                     )
    ORDER BY M1.AlphaID
    IF @MaxID IS NULL
        RETURN 'AAAAA'
    RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
END
GO
 
0

Вам нужно будет сохранить последний выделенный идентификатор в последовательности.

Например, сохраните его в другой таблице, содержащей один столбец и одну строку.

 CREATE TABLE CurrentMaxId (
    Id CHAR(6) NOT NULL
);

INSERT INTO CurrentMaxId (Id) VALUES ('AAAAAA');
 

Каждый раз, когда вы выделяете новый идентификатор, вы извлекаете значение из этой крошечной таблицы, увеличиваете его и сохраняете это значение в своей основной таблице, а также обновляете значение в CurrentMaxId.

Обычные предостережения относительно параллелизма, блокировки таблиц и т. д.