如何查询Bookmarks表中可用的下一个BookmarkNumber值？

问题说明 #

我有一个名为Bookmarks的表，其中包含smallint类型的BookmarkNumber列。书签支持创建和删除操作，当创建新书签（例如Id为"oo"）时，需要为新记录分配下一个可用的最小BookmarkNumber，示例数据中该值应为4。

示例数据 #

Id | BookmarkNumber #

dd | 1
aa | 2
dd | 3
tt | 5
ff | 27
uu | 28

查询方案 #

通用SQL方案（适配多数数据库） #

优先查找最小的缺失值，若所有值连续则取最大值+1，同时兼容表为空的情况：

SELECT COALESCE(
    -- 查找第一个缺失的最小可用值
    (SELECT MIN(b1.BookmarkNumber) + 1
     FROM Bookmarks b1
     WHERE NOT EXISTS (
         SELECT 1 FROM Bookmarks b2
         WHERE b2.BookmarkNumber = b1.BookmarkNumber + 1
     ) AND b1.BookmarkNumber >= 1),
    -- 若所有值连续，取最大值+1；表为空时返回1
    (SELECT COALESCE(MAX(BookmarkNumber), 0) + 1 FROM Bookmarks)
) AS NextBookmarkNumber;

窗口函数方案（支持窗口函数的数据库：PostgreSQL、SQL Server 2012+等） #

利用LEAD窗口函数快速定位缺失间隔：

SELECT COALESCE(
    -- 找到第一个存在间隔的位置，取当前值+1
    (SELECT BookmarkNumber + 1
     FROM (
         SELECT BookmarkNumber,
                LEAD(BookmarkNumber) OVER (ORDER BY BookmarkNumber) AS NextNum
         FROM Bookmarks
     ) t
     WHERE NextNum > BookmarkNumber + 1
     ORDER BY BookmarkNumber
     LIMIT 1),
    -- 无间隔时取最大值+1，表为空返回1
    (SELECT COALESCE(MAX(BookmarkNumber), 0) + 1 FROM Bookmarks)
) AS NextBookmarkNumber;

内容的提问来源于stack exchange，提问作者Hank Rearden