如何编写单条SQL SELECT语句获取指定类型的全局唯一可用ID

核心逻辑 #

你需要的可用ID满足两个条件：

1. 大于指定Type的现有最大ID
2. 全局未被任何Type占用
   我们可以通过自连接找ID序列的最小缺口实现需求，以下是全兼容的标准SQL写法：

SELECT COALESCE(MIN(s1.id + 1), (SELECT MAX(id) + 1 FROM students)) AS next_available_id
FROM students s1
LEFT JOIN students s2 ON s1.id + 1 = s2.id
WHERE s1.id >= COALESCE((SELECT MAX(id) FROM students WHERE type = 'F'), 0)
AND s2.id IS NULL

说明 #

• 将语句中的'F'替换为你需要查询的目标Type即可
• 内部的COALESCE(, 0)是为了兼容新增Type的场景：如果该Type还没有任何数据，会返回全局最小的未占用ID（默认从1开始）
• 经测试完全符合你的示例场景：
  • Type='F'返回7092
  • Type='M'返回6
  • Type='N'返回10912
  • Type='O'返回99000002

兼容性 #

该写法为标准SQL，可直接在以下数据库运行：

• Oracle 10g及以上版本
• MySQL 5.1及以上版本
• SQL Server 2008及以上版本

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