能否得出序列中空掉的号码

Question

select 100000+rownum-1 temprow from  dba_objects
 where 100000+rownum-1 between 100000  and 100007 
 and 100000+rownum-1 not in (select Id from 你的表)

Answer 1

SQL> select rnum from (
  2  select rownum+t.mid-1 rnum from all_source,(select min(id) mid,max(id) mxid from t) t
  3  where rownum<t.mxid) tt,t ttt where tt.rnum=ttt.id(+) and ttt.id is null;      RNUM
----------
         6
         8SQL> select id from t;        ID
----------
         1
         2
         3
         4
         5
         7
         9
        10已选择8行。SQL>

Answer 2

to freddy2003()  你这种写法会误导新手的，rownum不能那么用的。
to bzszp(SongZip) 这种办法对于小表好用，但对于大表而言就不行了。一般，这种ID都是有主键约束的，因此，我的方案：SELECT A.N-1 FROM TEST A WHERE NOT EXISTS(SELECT 1 FROM TEST B WHERE A.N=B.N+1)
AND A.N>(SELECT MIN(N) FROM TEST)
UNION
SELECT A.N+1 FROM TEST A WHERE NOT EXISTS(SELECT 1 FROM TEST B WHERE A.N=B.N-1)
AND A.N<(SELECT MAX(N) FROM TEST);简单来说：就是查找 没有直接前驱 和 没有直接后继 的数值

Answer 3

用法各有千秋，只要自己加工修改！
select id from tablename  a 
where not exits(select count(*) from tablename b where b.id = (a.id+1));
当然可以自己在两个地方都加上范围，有索引速度不会慢的！考虑不同用处也可以通过过程处理！

Answer 4

/*
boydgmx(梦霄)的方法也不太可行，如果中间缺的号不止一个的话，那就会漏掉数据。
<<<简单来说：就是查找 没有直接前驱 和 没有直接后继 的数值
正是这种方式导致了只看前一个和后一个，如果中间缺了不止一个号码呢？
*/
--方法：（简单来说：就是查找 没有直接前驱 和 没有直接后继 的数值）
--生成表：
CREATE TABLE TMP_TB_TEST
(
  ID NUMBER(10) NOT NULL
) TABLESPACE SYSTEM;--生成数据：
INSERT INTO TMP_TB_TEST VALUES(1);
INSERT INTO TMP_TB_TEST VALUES(9);--查找缺号：
SELECT A.ID-1 FROM TMP_TB_TEST A 
WHERE NOT EXISTS
(
  SELECT 1 FROM TMP_TB_TEST B WHERE A.ID = B.ID + 1
)
AND A.ID >
(
  SELECT MIN(ID) FROM TMP_TB_TEST
)
UNION
SELECT A.ID+1 FROM TMP_TB_TEST A 
WHERE NOT EXISTS
(
  SELECT 1 FROM TMP_TB_TEST B WHERE A.ID=B.ID-1
)
AND A.ID < 
(
  SELECT MAX(ID) FROM TMP_TB_TEST
);

Answer 5

/*
boydgmx(梦霄)的方法也不太可行，如果中间缺的号不止一个的话，那就会漏掉数据。
<<<简单来说：就是查找 没有直接前驱 和 没有直接后继 的数值
正是这种方式导致了只看前一个和后一个，如果中间缺了不止一个号码呢？
*/
--方法：（简单来说：就是查找 没有直接前驱 和 没有直接后继 的数值）
--生成表：
CREATE TABLE TMP_TB_TEST
(
  ID NUMBER(10) NOT NULL
) TABLESPACE SYSTEM
Table created
 
--生成数据：
INSERT INTO TMP_TB_TEST VALUES(1)
1 row inserted
 
 
INSERT INTO TMP_TB_TEST VALUES(9)
1 row inserted
 
 
--查找缺号：
SELECT A.ID-1 FROM TMP_TB_TEST A
WHERE NOT EXISTS
(
  SELECT 1 FROM TMP_TB_TEST B WHERE A.ID = B.ID + 1
)
AND A.ID >
(
  SELECT MIN(ID) FROM TMP_TB_TEST
)
UNION
SELECT A.ID+1 FROM TMP_TB_TEST A
WHERE NOT EXISTS
(
  SELECT 1 FROM TMP_TB_TEST B WHERE A.ID=B.ID-1
)
AND A.ID <
(
  SELECT MAX(ID) FROM TMP_TB_TEST
)
    A.ID-1
----------
         2
         8
2 rows selected
 
 

Answer 6

都大半年的贴了. 
我也写一个
SQL> select * from t;        ID
----------
      1000
      1001
      1003
      1004
      1005
      1007
      10207 行 已选择SQL> select level + (select min(id) from t) id from dual connect by level<=(select max(id)-min(id) from t) minus select id from t;        ID
----------
      1002
      1006
      1008
      1009
      1010
      1011
      1012
      1013
      1014
      1015
      1016
      1017
      1018
      101914 行 已选择SQL>

Answer 7

楼上的各位大侠累不累?
假设序列是100000开始的:
select rownum+100000-1 from all_objects 
where rownum<=(select max(id)-100000 from your_tab)
minus
select id from your_tab;

Answer 8

to nowait(独行天涯路):
如果  max(your_tab.id) > count(all_objects.*) 呢？
那还是会漏掉很多呀？
-------------
其实用集合的方法就很简单：
S(min(id)..max(id))-----这是全部的数据，记为集合S，共有max(id)-min(id))条数据
减去select id from your_tab这个集
---nowait(独行天涯路)就是用这种方法
但关键是集合S的数据怎么产生呢？

Answer 9

TO cenlmmx(学海无涯苦作舟) :
我执行select level + (select min(id) from t) id from dual connect by level<=(select max(id)-min(id) from t) minus select id from t;时报错：
ORA-01473: cannot have subqueries in CONNECT BY clause
请问是不是ORACLE版本不支持的原因，我是9i，你是10吧？