面试归来！ORACLE题目

2010-07-07 04:13:25 566 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MacBook 上 LaTeX 环境搭建：从 Mactex 到 Sublime Text 与 Skim 的保姆级配置流程本文详细介绍了在MacBook上搭建LaTeX环境的完整流程，从Mactex安装到Sublime Text与Skim的配置优化。涵盖基础环境准备、编辑器设置、PDF预览同步等关键步骤，帮助用户快速建立高效的LaTeX工作流，特别适合学术写作和技术文档创作。阅读详情

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

...全文

MEME-CHIP实战：5步搞定ChIP-seq数据中的motif发现与可视化本文提供了使用MEME-CHIP工具进行ChIP-seq数据motif发现与可视化的完整实战指南。通过5个清晰步骤，从数据准备、环境搭建、命令行执行到结果解读与高级可视化，详细讲解了如何利用MEME-CHIP工作流高效完成从原始peak序列到发表级motif图的分析，并分享了关键的参数调优与避坑经验。阅读详情

duqiangcise

基础和管理 17,377 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

qq82296344 2010-07-09

打赏
举报



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...  

这个应该可以实现了,一次只填一个连续的空.

sunqiusong 2010-07-09

打赏
举报

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

重建undo表空间
[/Quote]

sunqiusong 2010-07-09

打赏
举报

[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]
正解，学习了

qq82296344 2010-07-09

打赏
举报

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

zjhiphop2006 2010-07-08

打赏
举报

[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]
这个是正解！顶这个！！

xiaosheng2008 2010-07-08

打赏
举报

重建undo表空间

duqiangcise 2010-07-08

打赏
举报

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

tangren 2010-07-08

打赏
举报

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

duqiangcise 2010-07-08

打赏
举报

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

tangren 2010-07-08

打赏
举报

凑热闹，写一个

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

yu15tian 2010-07-08

打赏
举报

好诡异的问题，参考：



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;

shuiqingtinglin 2010-07-08

打赏
举报



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;

貌似这个符合要求吧？

qinfei008 2010-07-08

打赏
举报

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)

小灰狼W 2010-07-07

打赏
举报

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

orbcle 2010-07-07

打赏
举报

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

ganquan1104 2010-07-07

打赏
举报

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

liukai_happy 2010-07-07

打赏
举报

顶！！！！

xiaohu8855 2010-07-07

打赏
举报

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

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

duqiangcise 2010-07-07

打赏
举报

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

kikusong 2010-07-07

打赏
举报

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

加载更多回复（27）

面试归来！ORACLE题目

相关推荐