with a as( select 1 as id , 'aa' as name from dual union all select 2 as id , 'bb' as name from dual union all select 3 as id , 'bb' as name from dual union all select 9 as id , 'cc' as name from dual) select min(main.id) from (select sub.id id from (select a_min.a_min, (a_min.a_min+level-1) id from (select min(id) a_min from a )a_min, (select max(id) a_max from a )a_max connect by level <= a_max.a_max) sub where not exists (select 1 from a where a.id = sub.id) )main
1楼的结果是:4; 如果你的数据是: with a as( select 1 as id , 'aa' as name from dual union all select 3 as id , 'bb' as name from dual union all select 9 as id , 'cc' as …… 那么运行1楼的结果是:2 --总是取中间不存在的、最小的自然数
你写的好复杂,,, select NVL(min(ID+1), 01) yun from test a where exists (select Id from test b where a.id = b.id) and not exists (select Id from test c where c.id = a.id + 1)这样行吗?
借用一下兰老师的表 with a as( select 1 as id , 'aa' as name from dual union all select 2 as id , 'bb' as name from dual union all select 3 as id , 'bb' as name from dual union all select 5 as id , 'bb' as name from dual union all select 9 as id , 'cc' as name from dual) select b.mi from (select a.*, rownum mi from a) b where b.id <> b.mi and rownum = 1 order by b.id
有点错了,修改一下 with a as( select 1 as id , 'aa' as name from dual union all select 2 as id , 'bb' as name from dual union all select 3 as id , 'bb' as name from dual union all select 5 as id , 'bb' as name from dual union all select 9 as id , 'cc' as name from dual) select b.mi from (select a.id, rownum mi from a order by a.id) b where b.id <> b.mi and rownum = 1 order by b.id
select 1 as id , 'aa' as name from dual union all
select 2 as id , 'bb' as name from dual union all
select 3 as id , 'bb' as name from dual union all
select 9 as id , 'cc' as name from dual)
select min(main.id) from
(select sub.id id
from
(select a_min.a_min, (a_min.a_min+level-1) id
from (select min(id) a_min from a )a_min,
(select max(id) a_max from a )a_max
connect by level <= a_max.a_max) sub
where not exists (select 1 from a where a.id = sub.id)
)main
如果你的数据是:
with a as(
select 1 as id , 'aa' as name from dual union all
select 3 as id , 'bb' as name from dual union all
select 9 as id , 'cc' as ……
那么运行1楼的结果是:2
--总是取中间不存在的、最小的自然数
select NVL(min(ID+1), 01) yun
from test a
where exists (select Id from test b where a.id = b.id)
and not exists
(select Id from test c where c.id = a.id + 1)这样行吗?
如果只是取固定的一个最小值,手工查一下就可以了,比一堆SQL简单多了。不固定的话,情况就复杂了。 插入多条数据,说白了,就是把缺失的ID补齐,再顺序增加新的ID。楼主,出来说句话吧。
with a as(
select 1 as id , 'aa' as name from dual union all
select 2 as id , 'bb' as name from dual union all
select 3 as id , 'bb' as name from dual union all
select 5 as id , 'bb' as name from dual union all
select 9 as id , 'cc' as name from dual)
select b.mi
from (select a.*, rownum mi from a) b
where b.id <> b.mi
and rownum = 1
order by b.id
with a as(
select 1 as id , 'aa' as name from dual union all
select 2 as id , 'bb' as name from dual union all
select 3 as id , 'bb' as name from dual union all
select 5 as id , 'bb' as name from dual union all
select 9 as id , 'cc' as name from dual)
select b.mi
from (select a.id, rownum mi from a order by a.id) b
where b.id <> b.mi
and rownum = 1
order by b.id