查找“滚动”中的最后一个值 与存储过程的顺序？

Question

假设我有一组固定长度的字母字符标识符，例如总是五个字母，并且它们的分配方式是它们总是按顺序递增（GGGGZ --> GGGHA 等）。 现在，如果我到达 ZZZZZ，由于长度是固定的，我必须“翻转”到 AAAAA。 我可能有一个从 ZZZAA 到 AAAAM 的连续块。 我想编写一个存储过程来为我提供“下一个”标识符，在本例中为 AAAAN。

当然，如果我没有这个“滚动”问题，我只需按 DESC 排序并获取最高结果。 但我现在有点不知所措——SQL 不是我最擅长的语言，这一点也于事无补。

如果我有，我可以将其移至我的 C# 调用代码，但存储过程会更合适。

ETA：我想避免更改架构（新列或新表）； 我宁愿能够“弄清楚”。 我什至可能更喜欢使用蛮力（例如从最低值开始并递增，直到找到“洞”），尽管这可能会很昂贵。 如果您有一个不修改架构的答案，那么这将是满足我需求的更好解决方案。

Answer 1

我认为这里的代码将为您提供下一个值。 我创建了 3 个函数。 该表只是我对 table.column 和 alpha id 的模拟（我使用了 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

Answer 2

您必须存储序列中最后分配的标识符。

例如，将其存储在另一个具有一列和一列的表中。 一排。

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

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

每次分配新标识符时，您都会获取该小表中的值，递增该值，然后将该值存储在主表中，并更新 CurrentMaxId 中的值。

通常的注意事项适用于并发、表锁定等。

Answer 3

我想我会尝试将序列存储为整数，然后将其转换为字符串。 或者存储与 alpha 值同时递增的并行整数列。 无论哪种方式，您都可以对整数列进行排序。

Answer 4

这里的一个问题是，您无法真正从数据中判断“最后”条目在哪里，除非有更多关于如何删除旧条目的详细信息。

如果我理解正确的话，您将在序列的末尾环绕，这意味着您必须删除一些旧数据以腾出空间。 但是，如果数据没有以完全统一的方式删除，您最终会得到碎片，如下所示：

ABCD   HIJKL NOPQRS   WXYZ

您会注意到没有明显的下一个值...D 可能是最后创建的值，但也可能也可以是 L 或 S。

最多您可以查找第一个或最后一个缺失元素（使用存储过程执行 x+1 检查，就像在整数序列中查找缺失元素一样），但它不会为滚动列表提供任何特殊结果。

Answer 5

由于我不想编写代码来递增字母，因此我会创建一个包含所有有效 ID（AAAAAA 到 ZZZZZZ）的表，并为这些 ID 使用从 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），以防您的“编号”算法发生变化。

如果您确实想把事情做好，您可以按照建议重新进行数据库设计。

Answer 6

我认为满足我的需求影响最小的解决方案是添加一个标识列。 我可以保证的一件事是，排序将首先添加应该“首先出现”的条目——我永远不会添加带有标识符 BBBB 的条目，然后再返回并添加 BBBA。 如果我没有这个约束，显然它不会工作，但就目前情况而言，我可以通过标识列进行排序并获得我想要的排序。

我会继续思考其他建议——也许如果它们在我脑海中“响起”，它们看起来会是一个更好的选择。

Answer 7

要返回给定 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，则返回第一个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

，即对于 AAA 返回 BBB，对于 BBB 返回 CCC 等，并且，最后，AAA 代表 EEE，它位于表中的最后。