面试归来！ORACLE题目

[duqiangcise]

有一表t(id),现有如下问题：
t表中字段id的值如下：
1
2
3
5
6
9
10
11
12
13
16
问：能否有一句sql实现如下功能：
每执行一下该sql能够把id中缺少的值补上，例如：当第一次执行该sql时，会向t表中插入4，第二次执行该sql时，会向该表中
插入7，8；第三次执行时，会插入14，15.
求解！
我当时面试时，说的是我不能够用一条sql实现该功能，但可以通过oracle的过程，利用cursor来实现该功能。
看各位大侠是否有高解！

[kikusong]

无论怎样严防密守，面试题还是会流出来的。。。。。。。。。。题库。。。。。。

[duqiangcise]

去面试时，申请的是java职位，我估计那个面试官也是搞数据库的，最后面试时基本上都问我的是数据库的问题。面试完后，我告诉面试官，我在平常生活中对oracle很感兴趣。

[xiaohu8855]

[Quote=引用 26 楼 zhangcunhua 的回复:]
SQL code

insert into t
Select Id - Level
From (Select Id, p_Id
From (Select Id, p_Id
From (Select Id, Id - Lag(Id) Over(Order By Id) p_Id From t) T1
……
[/Quote]

好像就这个是对的啊，其他的都不满足你题目的要求啊，或者你没表述清楚？？

[liukai_happy]

顶 ！！！！

[ganquan1104]

这道题翻译一下，就是要你用一句sql找出表中的一组数据，这组数据必须满足以下条件：
1，他们是表中没有的数据中最小的
2，他们是连续的

[orbcle]

insert into t
select rn from(
select rn,
sum(t.id) over(order by 1 rows between unbounded preceding and current row) sum_id,
sum(rn) over(order by 1 rows between unbounded preceding and current row) sum_val
from (
select t.id,row_number() over(order by 1) rn from t
)
where sum_id = sum_val+1
);
这个怎么样，
我在家写的没验证
基本思想就是用row_number()的累计值和id的累计值相比较
累计值差1的时候就插入该值

[小灰狼W]

insert into t
WITH tmp1 AS(
  SELECT max(id)+1 startid
  FROM t
  START WITH ID=1
  CONNECT BY ID=PRIOR ID+1)
,tmp2 AS(
  SELECT MIN(ID)-1 endid
  FROM t
  WHERE ID>(SELECT startid FROM tmp1))
SELECT startid+ROWNUM-1 FROM tmp1
connect by rownum<=(select endid-tmp1.startid+1 from tmp2);

[qinfei008]

insert into testqf 
select  rn
  from (select rownum rn from testqf )
 where rn <
       (        
        select min(id)
          from (select id, rownum rn from (select id from testqf order by id))
         where id <> rn)
   and rn >
       (select max(id)
          from (select id, rownum rn from (select id from testqf order by id))
         where id = rn)

[shuiqingtinglin]

create table test(id number);
insert into TEST (ID)values (1);
insert into TEST (ID)values (2);
insert into TEST (ID)values (3);
insert into TEST (ID)values (4);
insert into TEST (ID)values (8);
insert into TEST (ID)values (9);
insert into TEST (ID)values (10);
--
insert into test
select id-1 from (select id,num from (select id,rownum num from (select id from test order by id) t))
where id!=num and rownum=1;

貌似这个符合要求吧？

[yu15tian]

好诡异的问题，参考：

CREATE TABLE t(ID NUMBER(2));
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(3);
INSERT INTO t VALUES(5);
INSERT INTO t VALUES(6);
INSERT INTO t VALUES(9);
INSERT INTO t VALUES(11);
COMMIT;

DECLARE 
v_1 NUMBER(2);
v_2 NUMBER(2);
v_3 NUMBER(2);
BEGIN
v_1:=1;
LOOP 
SELECT COUNT(*) INTO v_2 FROM t WHERE ID = v_1;
EXIT WHEN v_2=0;
v_1:=v_1+1;
END LOOP;
SELECT MIN(ID) INTO v_3 FROM t WHERE ID >v_1;
FOR i IN v_1..v_3-1 LOOP
INSERT INTO t(ID) VALUES(i);
END LOOP;
COMMIT;
END;
/
SELECT * FROM t ORDER BY ID;

[tangren]

凑热闹，写一个

INSERT INTO t(id)
  SELECT LEVEL + id1
    FROM (SELECT MIN(id) id1, MIN(lid) id2
            FROM (SELECT id, lead(id) over(ORDER BY id) lid FROM t)
           WHERE lid - id <> 1)
  CONNECT BY LEVEL < id2 - id1

[duqiangcise]

+40分！
忘大家继续发表言论！

[tangren]

个人感觉，面试官如果考这种题目，有点钻牛角尖，
并且面试JAVA职位，问这种题目就显得不厚道了。

[duqiangcise]

[Quote=引用 42 楼 tangren 的回复:]
个人感觉，面试官如果考这种题目，有点钻牛角尖，
并且面试JAVA职位，问这种题目就显得不厚道了。
[/Quote]
我估计面试官是干数据库的或对数据库比较熟悉！
他前边也问了我几个数据库的题，他可能看我答得比较可以，所以可能就把兴趣给他掉上来了。

[xiaosheng2008]

重建undo表空间

[zjhiphop2006]

[Quote=引用 34 楼 wildwave 的回复:]

SQL code
insert into t
WITH tmp1 AS(
SELECT max(id)+1 startid
FROM t
START WITH ID=1
CONNECT BY ID=PRIOR ID+1)
,tmp2 AS(
SELECT MIN(ID)-1 endid
FROM t
WHERE ID>(SELECT startid FROM tmp……
[/Quote]
这个是正解！顶这个！！

[qq82296344]

[code=SQLcreate table a as (
select * from (
select level id from dual connect by level < 20 ) where id in (1,
3,
4,
5,
6,
10,
11,
12,
13,
14,
17,
18,
19,
9))
;
insert into a (
select * from
(
with temp_table as
(select s.id
from (select rownum as id from dual connect by rownum<= (select max(id) from a)) s
left join a on a.id= s.id
where a.id is null )

select id
from temp_table
where id >=(select min(id) from temp_table )
and id < (select min(a.id) from a where a.id>(select min(id) from temp_table ))
)

);
][/code]

[sunqiusong]

[Quote=引用 33 楼 orbcle 的回复:]

insert into t
select rn from(
select rn,
sum(t.id) over(order by 1 rows between unbounded preceding and current row) sum_id,
sum(rn) over(order by 1 rows between unbounded preceding and current r……
[/Quote]
正解，学习了

[sunqiusong]

顶，这个正解
[Quote=引用 35 楼 xiaosheng2008 的回复:]

重建undo表空间
[/Quote]

[qq82296344]

create table as test (select * from (
select * from (
select t.*, t1.*, t2.*
  from (select level as id from dual connect by level < 20 )  t
  left join (select *
               from (select level as t_id from dual connect by level < 20) tt
              where tt.t_id not in (3, 4, 6, 8, 11, 12, 17)
             
             ) t1 on t.id = t1.t_id
  left join (select *
               from (select level as tid from dual connect by level < 20) tt
              where tt.tid in (3, 4, 6, 8, 11, 12, 17)) t2 on t.id = t2.tid) order by id )



;

 
create table  t  as  ( 
select id i_d,c_ from (
select t.*,id-t_id as c_ from (
select id , rownum t_id , tid from (
select  * from test where tid is not null   order by id  )) t));



update test set t_id = id where t_id is null and id < (

 
select id + cc_
  from (select tt.*,
               row_number() over(partition by t_id, c_ order by id desc) cc_
          from (select t1.*, t.*
                  from test t1
                  left join t on t1.id = t.i_d
                 order by t1.id) tt
         order by id)
 where id in
       ( select min(id)
          from (select tt.*,
                       row_number() over(partition by t_id, c_ order by id desc) cc_
                  from (select t1.*, t.*
                          from test t1
                          left join t on t1.id = t.i_d
                         order by t1.id) tt
                 order by id)
         where t_id is null )
);
写了一小时,呵呵  估计要是我面试的话,就over了,o(∩_∩)o...  
这个应该可以实现了,一次只填一个连续的空.