请教查询语句的写法？

assion

最初由 lastwinner 发布
[B]最简单的SQL,仅有一层嵌套
select distinct s+level-1 rlt from (select lag(n,1)over(order by n)+1 S, n-1 E from ttt) where E-S+1<>0 connect by level<=e-s+1; [/B]

这个方法确定不错～

hrong1982

佩服啊

hhd_1976

db2 下有类似oracle下的lag函数吗？

www.wwf.co

another method:
SQL> select * from
  2  (select rownum rn from dual connect by rownum <= (select max(id) from t))
  3  where rn not in
  4  (select id from t);

        RN
----------
         5
         7

db2001

最初由 www.wwf.co 发布
[B]another method:
SQL> select * from
  2  (select rownum rn from dual connect by rownum <= (select max(id) from t))
  3  where rn not in
  4  (select id from t);

        RN
----------
         5
         7 [/B]

这个思路可以.

orablue

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS


SQL> create table t
  2  (a number) ;

Table created

SQL> insert into t values (1);

1 row inserted

SQL> insert into t values (2);

1 row inserted

SQL> insert into t values (3);

1 row inserted

SQL> insert into t values (4);

1 row inserted

SQL> insert into t values (6);

1 row inserted

SQL> insert into t values (8);

1 row inserted

SQL> insert into t values (9);

1 row inserted

SQL> insert into t values (10);

1 row inserted

SQL> commit ;

Commit complete

SQL>
SQL> select m.*
  2  from (
  3  select rownum rnum
  4  from all_objects
  5  where rownum<=(select max(a) from t)
  6    and rownum>=(select min(a) from t)
  7    ) m
  8  where m.rnum not in (select a from t)
  9  ;

      RNUM
----------
         5
         7

SQL>


我也凑一个热闹.

mzwilson

？？
我用你们的方法：
SQL> select * from
2 (select rownum rn from dual connect by rownum <= (select max(id) from t))
3 where rn not in
4 (select id from t);

报错提示Connect By 子句中不能具有子查询
另外，我用：
SQL> select * from (select rownum rnum  from dual  where rownum<=(select max(dep
tno) from test)  and rownum>=(select min(deptno) from test) )m
这个方法返回的是空行？？？
为什么啊？

lsfredhat

经过上面改造的：
select S from (select lag(serial_no,1) over(order by serial_no)+1 S, serial_no E from t6) where E-S<>0

jvkojvko

在网站上有这个的一个话题的