哥哥们 求sql

[lizhilongshandong]

表1ID有1，3，9
我再往表1插入时 ID我想取2
因为
2没有并且 2是没有中的最小的。。。（2<4<5<6....）

速度啊

[yixilan]

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

[yixilan]

[Quote=引用 1 楼 yixilan 的回复:]
SQL code
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 ……
[/Quote]
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
--总是取中间不存在的、最小的自然数

[flyandfade]

借用一下兰老师的表
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

[flyandfade]

有点错了，修改一下
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

[lizhilongshandong]

你写的好复杂，，，
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)

这样行吗？

[coolkisses]

楼主的问题，不认为只是这么简单的。
如果只是取固定的一个最小值，手工查一下就可以了，比一堆SQL简单多了。

不固定的话，情况就复杂了。 插入多条数据，说白了，就是把缺失的ID补齐，再顺序增加新的ID。

楼主，出来说句话吧。

[lizhilongshandong]

当然不固定 固定的话 还查它干嘛 这个题 还有约束 最大数为99 当达到99时 且前面都已填满 不再增加
数据ID都以两位数显示