求！oracle中查找出不连接的，即丢失的id号码

[yunfeixue520]

比如id ：1 2 3 4 6 7 8 10 15 16 17.......
如何查找出中间丢失的5 9 11 12 13 14.......

[终端用户]

select id  from (select level id from dual connect by level <= 17) temp where id  not in (1,2, 3, 4, 6, 7, 8, 10, 15, 16, 17)

[终端用户]

----------------------------
5
9
11
12
13
14

[yunfeixue520]

那些数字只是我为了简单说明问题，而实际数据库中的数字是很大的，不可能吧那些数字一一写出来。所以在select语句中不可以直接用数字的。
[Quote=引用 1 楼 yy_mm_dd 的回复:]
SQL code

------Like this, gook luck.
SQL> with a as
2 (
3 select 1 id from dual union all
4 select 2 id from dual union all
5 select 3 id from dual union all
6 select 4……
[/Quote]

[yunfeixue520]

数据在id字段中，要查找出来
才知道
[Quote=引用 1 楼 yy_mm_dd 的回复:]
SQL code

------Like this, gook luck.
SQL> with a as
2 (
3 select 1 id from dual union all
4 select 2 id from dual union all
5 select 3 id from dual union all
6 select 4……
[/Quote]

[gelyon]

[Quote=引用 7 楼 yunfeixue520 的回复:]

那些数字只是我为了简单说明问题，而实际数据库中的数字是很大的，不可能吧那些数字一一写出来。所以在select语句中不可以直接用数字的。
引用 1 楼 yy_mm_dd 的回复:
SQL code

------Like this, gook luck.
SQL> with a as
2 (
3 select 1 id from dual union all
4 se……
[/Quote]

我们那也是给的测试数据而已，你直接用后面select语句就可以了，from对应你实际的表

[yunfeixue520]

谢谢，貌似可以。不过有点不理解什么意思。
[Quote=引用 2 楼 gelyon 的回复:]
SQL code


WITH tab AS(
SELECT level tid FROM dual CONNECT BY LEVEL<=100)
SELECT tid FROM tab a
WHERE NOT EXISTS(SELECT 1 FROM your_table_name WHERE a.tid=id)
[/Quote]

[yunfeixue520]

恩，在进行中
[Quote=引用 9 楼 gelyon 的回复:]
引用 7 楼 yunfeixue520 的回复:

那些数字只是我为了简单说明问题，而实际数据库中的数字是很大的，不可能吧那些数字一一写出来。所以在select语句中不可以直接用数字的。
引用 1 楼 yy_mm_dd 的回复:
SQL code

------Like this, gook luck.
SQL> with a as
2 (
3 select 1 id from ……
[/Quote]

[suhenhappy]

观看中。。。

[YY_MM_DD]

[Quote=引用 11 楼 yunfeixue520 的回复:]

恩，在进行中
引用 9 楼 gelyon 的回复:
引用 7 楼 yunfeixue520 的回复:

那些数字只是我为了简单说明问题，而实际数据库中的数字是很大的，不可能吧那些数字一一写出来。所以在select语句中不可以直接用数字的。
引用 1 楼 yy_mm_dd 的回复:
SQL code

------Like this, gook luck.
SQL> with a……
[/Quote]

你有时间去看看connect by的用法吧。。。你现在就可以简单的理解为循环多少次。。

[YY_MM_DD]

------Like this, gook luck.
SQL> with a as
  2  (
  3    select 1 id from dual union all
  4    select 2 id from dual union all
  5    select 3 id from dual union all
  6    select 4 id from dual union all
  7    select 6 id from dual union all
  8    select 7 id from dual union all
  9    select 8 id from dual union all
 10    select 10 id from dual union all
 11    select 15 id from dual union all
 12    select 16 id from dual union all
 13    select 17 id from dual
 14  )
 15  select rownum from dual connect by rownum<=(select max(id) from a)
 16  minus
 17  select id from a
 18  ;

    ROWNUM
----------
         5
         9
        11
        12
        13
        14

6 rows selected

SQL> 

[gelyon]

WITH tab AS(
SELECT level tid FROM dual CONNECT BY LEVEL<=100)
SELECT tid FROM tab a
WHERE NOT EXISTS(SELECT 1 FROM your_table_name WHERE a.tid=id)

[gelyon]

with tmp as(
select 1 id from dual union all
select 2 id from dual union all
select 3 id from dual union all
select 4 id from dual union all
select 6 id from dual union all
select 7 id from dual union all
select 8 id from dual union all
select 10 id from dual union all
select 15 id from dual union all
select 16 id from dual union all
select 17 id from dual 
) ,
tab AS(
SELECT level tid FROM dual CONNECT BY LEVEL<=(SELECT Max(id) FROM tmp)
)
SELECT tid FROM tab a
WHERE NOT EXISTS(SELECT 1 FROM tmp WHERE a.tid=id)

[心中的彩虹]

[Quote=引用楼主 yunfeixue520 的回复:]
比如id ：1 2 3 4 6 7 8 10 15 16 17.......
如何查找出中间丢失的5 9 11 12 13 14.......
[/Quote]

SQL> with tb as
  2  (select 1 id from dual union all
  3  select 2  from dual union all
  4  select 3  from dual union all
  5  select 4  from dual union all
  6  select 6  from dual union all
  7  select 7  from dual union all
  8  select 8  from dual union all
  9  select 10  from dual union all
 10  select 15  from dual union all
 11  select 16  from dual union all
 12  select 17  from dual )
 13  (select level id
 14  from dual
 15  connect by level<=(select max(id) from tb)) minus select id from tb
 16  /
 
        ID
----------
         5
         9
        11
        12
        13
        14
 
6 rows selected
 

[andylist]

观看一下。。。