是这意思吗 with t as ( select 1 a from dual union select 2 a from dual union select 3 a from dual union select 5 a from dual union select 6 a from dual union select 8 a from dual )select t2.a from ( select rownum r,a from t ) t1 , ( select r-1 r ,a from( select rownum r ,a from t ) where r>1 ) t2 where t2.a - t1.a = 2 and t1.r =t2.r ****************** a --- 5 8
SQL> select table_name, blocks from user_tables order by table_name;TABLE_NAME BLOCKS ------------------------------ ---------- DEPT 5 EMP 5 BONUS 0 SALGRADE 5 SQL>select * from (select table_name, blocks, (lag(blocks) over(order by table_name) - blocks) minusCount from user_tables) where minusCount=2;
那是第一个数据的前一个是没有的,所以是null,null的任何计算都是false所以就没有了,可以用nvl转一下。 如SQL>select * from (select table_name, blocks, (nvl(lag(blocks) over(order by table_name), 0) - blocks) minusCount from user_tables) where minusCount=2;
select t2.a from ( select rownum r,a from t ) t1 , ( select r-1 r ,a from( select rownum r ,a from t ) where r>1 ) t2 where t2.a - nvl(t1.a,0) <> 2 and t1.r =t2.r(+)改的主要是最后两行 where t2.a - nvl(t1.a,0) <> 2 and t1.r =t2.r(+)
with t as
(
select 1 a from dual
union
select 2 a from dual
union
select 3 a from dual
union
select 5 a from dual
union
select 6 a from dual
union
select 8 a from dual
)select t2.a from
(
select rownum r,a
from t
) t1
,
(
select r-1 r ,a
from(
select rownum r ,a
from t
)
where r>1
) t2
where t2.a - t1.a = 2
and t1.r =t2.r
******************
a
---
5
8
------------------------------ ----------
DEPT 5
EMP 5
BONUS 0
SALGRADE 5
SQL>select * from (select table_name, blocks, (lag(blocks) over(order by table_name) - blocks) minusCount from user_tables) where minusCount=2;
如SQL>select * from (select table_name, blocks, (nvl(lag(blocks) over(order by table_name), 0) - blocks) minusCount from user_tables) where minusCount=2;
(
select rownum r,a
from t
) t1
,
(
select r-1 r ,a
from(
select rownum r ,a
from t
)
where r>1
) t2
where t2.a - nvl(t1.a,0) <> 2
and t1.r =t2.r(+)改的主要是最后两行
where t2.a - nvl(t1.a,0) <> 2
and t1.r =t2.r(+)