萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 查找自增 ID 中缺失的最小可用編號

場景

業務表 TT(ID INT PRIMARY KEY) 因刪除操作產生斷號(如 1,2,4,5)。新增記錄時希望復用最小缺口 ID(此例為 3),而非直接追加 MAX+1。

單語句解法

SELECT MIN(T.ID) AS MissingID
FROM (SELECT ID+1 AS ID FROM TT) T
WHERE T.ID NOT IN (SELECT ID FROM TT);

原理解析

  1. 子查詢 (SELECT ID+1 FROM TT) 生成「每個現有 ID 的下一個數」。
  2. 外層 NOT IN 過濾掉已存在的 ID,剩下的即為「緊跟在現有 ID 之後但未被佔用」的數字。
  3. MIN() 取最小值,即首個缺口。

大數據量優化版(避免 NOT IN 子查詢性能差)

-- 使用 LEFT JOIN / NOT EXISTS
SELECT MIN(T.NextID) AS MissingID
FROM (SELECT ID+1 AS NextID FROM TT) T
LEFT JOIN TT ON TT.ID = T.NextID
WHERE TT.ID IS NULL;

-- 或 NOT EXISTS(語義更清晰,計劃同 JOIN)
SELECT MIN(T.NextID)
FROM (SELECT ID+1 AS NextID FROM TT) T
WHERE NOT EXISTS (SELECT 1 FROM TT WHERE ID = T.NextID);

進階:批量獲取前 N 個缺口

WITH Gaps AS (
    SELECT ID+1 AS GapStart, LEAD(ID) OVER (ORDER BY ID) - 1 AS GapEnd
    FROM TT
)
SELECT GapStart AS MissingID
FROM Gaps
WHERE GapStart <= GapEnd
ORDER BY GapStart
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

利用窗口函數 LEAD() 一次掃描得出所有缺口區間,配合 OFFSET/FETCH 靈活取用。

copyright © 萬盛學電腦網 all rights reserved