Oracle通过SQL找出ID不连续的位置

1 前言

  工作中要求我找到 ID 不连续的地方, 然后拿这个 ID , 给数据库里面 INSERT 一条数据. 比如:

ID	备注
… 省略连续的部分
23
24
25	就是想找到这里断开的地方, 下一个可以 INSERT 的 ID 就是 26
74915
75115
75120
75122
… 省略连续的部分

2 SQL

  假设数据库表名叫 MY_TEST_TABLE, 先执行一段 SQL, 看看它 ID 不连续的位置:

SELECT t1.id FROM MY_TEST_TABLE t1 ORDER BY t1.id ASC
;
-- 结果
ID   |
-----+
... 省略连续的部分22|23|24|25|
74915|
75115|
75120|
... 省略连续的部分

  很明显, 由上面的执行结果可知, ID 在 25 和 74915 的位置 不连续了, 那么下一个可 INSERT 的 ID 就是 26. 接下来, 我们就执行 SQL 来找到这个 26 :

-- 查找 项目成员 的 不连续的 ID 的 SQL
SELECT
/* 下一个ID */
t3.before_id + 1 next_id
FROM (SELECT /* before_id 表示当前 ID 的上一个 真实的ID. 比如 25 */FIRST_VALUE(t2.ID) OVER (ORDER BY t2.id rows between 1 preceding and 1 following) before_id,/* before_id 表示当前 真实的ID. 比如 74915 */t2.id current_id,/* after_id 表示当前 ID 的下一个 真实的ID. 比如 75115 */LAST_VALUE(t2.id) OVER (ORDER BY t2.id rows between 1 preceding and 1 following) after_id FROM (SELECT t1.id FROM MY_TEST_TABLE t1 ORDER BY t1.id asc) t2
) t3
WHERE 
/* 上一个可能的ID(25) 加1 不等于当前的ID (75115) */
t3.before_id + 1 <> t3.current_id AND t3.current_id - 1 > 1
/* 只取第一个 */
AND rownum = 1
;
-- 结果
NEXT_ID|
-------+26|

3 结语

  需要注意的是, 上面的SQL 只适合 Oracle, 它不适合 MySQL, 因为 FIRST_VALUE() 和 LAST_VALUE() 函数 可能在 MySQL 上面没有.