若表结构:A(f1,f2,f3) B(f1,f2,f3) SQL> create table a(f1 integer,f2 varchar2(2),f3 integer);表已创建。SQL> create table b as select * from a;表已创建。SQL> begin 2 insert into a values(1,'a',5); 3 insert into a values(2,'a',5); 4 insert into a values(3,'a',6); 5 insert into b values(1,'a',-1); 6 insert into b values(2,'a',-1); 7 commit; 8 end; 9 /PL/SQL 过程已成功完成。SQL> select * from a; F1 F2 F3 ---------- -- ---------- 1 a 5 2 a 5 3 a 6已选择3行。 SQL> select * from b; F1 F2 F3 ---------- -- ---------- 1 a -1 2 a -2已选择2行。 SQL> select a.sum_f3+b.sum_f3 from 2 (select sum(f3) sum_f3 from a where a.f2='a') a, 3 (select sum(f3) sum_f3 from b where b.f2='a') b;A.SUM_F3+B.SUM_F3 ----------------- 13已选择 1 行。
.... SQL> select sum(t) from ( 2 select sum(f3) t from a where a.f2='a' 3 union all 4 select sum(f3) t from b where b.f2='a' 5 ); SUM(T) ---------- 13已选择 1 行。
select ((select sum(f3) from A where f2='a') - (select sum(f3) from B where f2='a')) from dual
SQL> create table a(f1 integer,f2 varchar2(2),f3 integer);表已创建。SQL> create table b as select * from a;表已创建。SQL> begin
2 insert into a values(1,'a',5);
3 insert into a values(2,'a',5);
4 insert into a values(3,'a',6);
5 insert into b values(1,'a',-1);
6 insert into b values(2,'a',-1);
7 commit;
8 end;
9 /PL/SQL 过程已成功完成。SQL> select * from a; F1 F2 F3
---------- -- ----------
1 a 5
2 a 5
3 a 6已选择3行。
SQL> select * from b; F1 F2 F3
---------- -- ----------
1 a -1
2 a -2已选择2行。
SQL> select a.sum_f3+b.sum_f3 from
2 (select sum(f3) sum_f3 from a where a.f2='a') a,
3 (select sum(f3) sum_f3 from b where b.f2='a') b;A.SUM_F3+B.SUM_F3
-----------------
13已选择 1 行。
SQL> select sum(t) from (
2 select sum(f3) t from a where a.f2='a'
3 union all
4 select sum(f3) t from b where b.f2='a'
5 ); SUM(T)
----------
13已选择 1 行。
((select sum(f3) from A where f2='a') - (select sum(f3) from B where f2='a')) from dual