给你个例子 WITH TEST AS( SELECT 1 AS N1,2 AS N2 FROM DUAL UNION ALL SELECT 3 AS N1,4 AS N2 FROM DUAL UNION ALL SELECT 5 AS N1,6 AS N2 FROM DUAL ) SELECT N1 - LAG(N2,1,0)OVER(ORDER BY N2) FROM TEST
with test as ( select 1000 as id , 300 as mid from dual union all select 1000 as id , 200 as mid from dual union all select 1000 as id , 200 as mid from dual ), test1 as ( select rownum rn , a.* from test a ) select a.id - b.mid as result from test1 a , test1 b where b.rn+1 = a.rn(+) order by a.rn--运行结果 -------------------- 700 800 null 你自己把上面的id改为日期相减哈。。
您的回复对我很有用 ,但是我所查的表里很多数据,我不能每条都写个UNION吧?那要写很多啊
汗。你没发现那test (临时表)表指代的就是你要查询的那张表 select 1000 as id , 300 as mid from dual
WITH TEST AS(
SELECT 1 AS N1,2 AS N2 FROM DUAL
UNION ALL
SELECT 3 AS N1,4 AS N2 FROM DUAL
UNION ALL
SELECT 5 AS N1,6 AS N2 FROM DUAL
)
SELECT N1 - LAG(N2,1,0)OVER(ORDER BY N2) FROM TEST
test as
(
select 1000 as id , 300 as mid from dual
union all
select 1000 as id , 200 as mid from dual
union all
select 1000 as id , 200 as mid from dual
),
test1 as
(
select rownum rn , a.* from test a
)
select a.id - b.mid as result from test1 a , test1 b where b.rn+1 = a.rn(+) order by a.rn--运行结果
--------------------
700
800
null 你自己把上面的id改为日期相减哈。。
您的回复对我很有用 ,但是我所查的表里很多数据,我不能每条都写个UNION吧?那要写很多啊
汗。你没发现那test (临时表)表指代的就是你要查询的那张表
select 1000 as id , 300 as mid from dual
正解,利用rownum和左连接解决问题