面试归来！ORACLE题目

Question

有一表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来实现该功能。
看各位大侠是否有高解！

Answer 1

insert into t (id)
select rownum from dual connect by rownum<=16 
minus
select id from t

Answer 2

同样的SQL每执行一次只补一个地方的缺漏?而不是运行一次全补上?

Answer 3

用一句sql实现，我能想到的思路只是这样啦，有没其他写法就一起等高人啦

Answer 4

变态出的问题。sql数据库中有自增列 。但是如果要删除这样数据 。再新增数据就会出现断点 。如目前有三条数据1，2，3  删除 3 .在增加则新增加的ID就变成4 .
此种问题对于提高编程没有意义

Answer 5

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
                 Where T1.p_Id <> 1) T2
         Where Rownum < 2) T3
Connect By Level < p_Id
不知怎么样？

Answer 6

想到可以用ROWNUM 实现..
类似如下
select min(rn) from (
   select id,rownum rn from (
       select id from a order by id)
)where id <> rn每次取出不匹配的就OK了...

Answer 7

insert into t 
select * from (
select rownum from dual connect by rownum<=(select max(id) from t)
minus
select id from t ) where rownum=1
这样可以吗

Answer 8

INSERT INTO T SELECT * FROM 
(
SELECT ID + 1  FROM T 
       WHERE ID + 1 NOT IN (SELECT ID FROM T) 
       AND ROWNUM < 2 
       AND ID + 1 < (SELECT MAX(ID) FROM T) ORDER BYID ASC        
)

Answer 9

insert into t
select min(rn) from (
   select id,rownum rn from (
       select id from a order by id)
)where id <> rn;
这个老兄的可以，但一次只能够插入一条缺省项。

Answer 10

我的可以一次完成插入全部缺少的数，不知道为什么要一次只插入一个数。效率的话可以考虑exists
insert into t
  select rn from (select rownum rn
    from dual
  connect by rownum <= (select max(id) from t))
   where rn not in (select id from t)

Answer 11

mysql版insert into t (id)
SELECT MIN(id)+1 FROM t a
WHERE id+1 NOT IN (SELECT id FROM t b );

Answer 12

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
                 Where T1.p_Id <> 1) T2
         Where Rownum < 2) T3
Connect By Level < p_Id
我这个不可以么？？
难道我理解错了！

Answer 13

 mysql版
 SELECT c.id FROM 
(SELECT MIN(a.id) AS id FROM t a WHERE a.id + 1 NOT IN(SELECT
                                 id
                               FROM t d)) r ,
 (SELECT   MIN(b.id) AS id  FROM       t b WHERE   b.id>1 AND b.id-1 NOT IN (SELECT id FROM t d)) q,
 (SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 2  ..... UNION ALL SELECT 16 ) c                   
 WHERE c.id>r.id AND c.id<q.id

Answer 14

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

Answer 15

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

Answer 16

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的时候就插入该值

Answer 17

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);

Answer 18

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)

Answer 19

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;
貌似这个符合要求吧？

Answer 20

好诡异的问题，参考：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;

Answer 21

凑热闹，写一个
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

Answer 22

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

Answer 23

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...  
这个应该可以实现了,一次只填一个连续的空.
 

Answer 24

[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]