使用存储过程查找“滚动”序列中的最后一个值?

下面是我认为将为您提供下一个值的代码。我创建了3个函数。这个表就是我用alpha ID模拟的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'

顺便说一句,如果你不想假设连续性,你可以按照你的建议去做(如果有“zzzz”行),使用序列中的第一个间隙。将最后一个函数替换为:

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

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

例如,将其存储在另一个有一列的表中&一排。

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

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

每次分配一个新的标识符时,您都会获取该小表中的值,增加该值,并将该值存储在主表中,同时更新该表中的值 CurrentMaxId

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

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

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

如果我理解正确的话,您将在序列的末尾换行,这意味着您必须删除一些旧数据以腾出空间。但是,如果数据没有以完全一致的方式删除,则最终会出现碎片,如下所示:

ABCD   HIJKL NOPQRS   WXYZ

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

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

因为我不想编写递增字母的代码,所以我会创建一个包含所有有效ID(aaaaa到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

如果表为空,则合并在那里。为了真正可靠,您应该计算“AAAAAA”(选择@min\u silly\u id=silly\u id,其中id=1),以防您的“编号”算法发生变化。

如果你真的想把事情做好,你应该按照建议重做数据库设计。

我认为对我的需求影响最小的解决方案是添加一个标识列。我可以保证的一件事是,排序将是这样的,即应该“先到”的条目将首先添加——我永远不会添加标识符为BBBB的条目,然后再返回并添加BBBA。如果我没有那个约束,显然它不会起作用,但就目前而言,我可以按标识列排序,得到我想要的排序。

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

返回下一个 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

此查询将搜索 身份证件 在给定的对象旁边。如果没有 身份证件 ,它返回第一个 .

结果如下:

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

我E它回来了 BBB 对于 AAA , CCC 对于 BBB 等等,最后, 对于 EEE