select a.*,null,null,null from a where name in (select name from a minus select name from b)
union
select null,null,null,b.* from b where name in (select name from b minus select name from a)
union
select null,null,null,b.* from b where name in (select name from b minus select name from a)
(
A DATE,
B NUMBER,
C NUMBER
)
/SQL> select * from test1;A B C
----------- ---------- ----------
2002-5-6 550
2002-5-8 660
2002-5-11 690
2002-6-1 700 SQL> select a,b,b-(select max(b) from test1 t1 where t1.a<t.a) c from test1 t;A B C
----------- ---------- ----------
2002-5-6 550
2002-5-8 660 110
2002-5-11 690 30
2002-6-1 700 10
CREATE TABLE SCOTT.TEST1
(
A DATE,
B NUMBER)
/SQL> select * from test1;A B
----------- ----------
2002-5-6 550
2002-5-8 660
2002-5-11 690
2002-6-1 700 SQL> select a,b,b-(select max(b) from test1 t1 where t1.a<t.a) c from test1 t;A B C
----------- ---------- ----------
2002-5-6 550
2002-5-8 660 110
2002-5-11 690 30
2002-6-1 700 10
from a1,a2
where a1.时间=a2.时间 -1
实在不行,可以通过存储过程,先写到临时表里,然后再从临时表里查询。
select a.a 日期,a.b 累计量,a.b-(select b.b from test10 b where b.a=a.a-1) 日累计量 from test10 a order by a
INSERT INTO T1 VALUES(SYSDATE-5,100);
INSERT INTO T1 VALUES(SYSDATE-4,150);
INSERT INTO T1 VALUES(SYSDATE-3,180);
INSERT INTO T1 VALUES(SYSDATE-2,220);
INSERT INTO T1 VALUES(SYSDATE-1,300);
COMMIT;SELECT A.RIQI,A.NUM,A.NUM-B.NUM
FROM T1 A,T1 B
WHERE TRUNC(A.RIQI)-1=TRUNC(B.RIQI(+));结果:
RIQI NUM A.NUM-B.NUM
---------- ---------- -----------
17-11月-02 100
18-11月-02 150 50
19-11月-02 180 30
20-11月-02 220 40
21-11月-02 300 80