with tb as( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 8 from dual) --以上为提供数据的语句 select min(max(id)+1) from tb group by rownum-idMIN(MAX(ID)+1) -------------- 3
--------好久没写了你看看这样行不 SQL> SQL> with tablename as 2 ( 3 select 1 no from dual union all 4 select 2 from dual union all 5 select 4 from dual union all 6 select 5 from dual union all 7 select 6 from dual union all 8 select 7 from dual union all 9 select 9 from dual union all 10 select 10 from dual 11 ) 12 select min(no) 13 from (select rownum no 14 from dual 15 connect by rownum <= (select max(no) from tablename) 16 minus 17 select no from tablename) 18 ; MIN(NO) ---------- 3SQL>
修改了下: with tmp as ( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 8 from dual ) select min(id) from (select level id from dual connect by level <= (select max(id) from tmp) +1 minus select id from tmp );
with tmp as ( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 8 from dual ) select min(id) from (select level id from dual connect by level <= (select max(id) from tmp) +1 minus select id from tmp );
with tmp as ( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 8 from dual ) select min(id) + 1 from (select id, lead(id) over(order by id) as id2 from tmp) where id2 - id <> 1
with tb as(
select 1 id from dual union all
select 2 from dual union all
select 4 from dual union all
select 5 from dual union all
select 7 from dual union all
select 8 from dual)
--以上为提供数据的语句
select min(max(id)+1)
from tb
group by rownum-idMIN(MAX(ID)+1)
--------------
3
--------好久没写了你看看这样行不
SQL>
SQL> with tablename as
2 (
3 select 1 no from dual union all
4 select 2 from dual union all
5 select 4 from dual union all
6 select 5 from dual union all
7 select 6 from dual union all
8 select 7 from dual union all
9 select 9 from dual union all
10 select 10 from dual
11 )
12 select min(no)
13 from (select rownum no
14 from dual
15 connect by rownum <= (select max(no) from tablename)
16 minus
17 select no from tablename)
18 ; MIN(NO)
----------
3SQL>
with tmp as
(
select 1 id from dual
union all
select 2 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 7 from dual
union all
select 8 from dual
)
select min(id)
from (select level id
from dual
connect by level <= (select max(id) from tmp) +1 minus
select id
from tmp
);
with tmp as
(
select 1 id from dual
union all
select 2 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 7 from dual
union all
select 8 from dual
)
select min(id)
from (select level id
from dual
connect by level <= (select max(id) from tmp) +1
minus
select id
from tmp
);
(
select 1 id from dual
union all
select 2 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 7 from dual
union all
select 8 from dual
)
select min(id) + 1
from (select id, lead(id) over(order by id) as id2 from tmp)
where id2 - id <> 1