求一个sql语句

Question

数据库是oracle 
table1                                         table2 
Key  ...    No ...  Quantity                 Key  ...    No ...   Quantity               
1           001        5                      1           001        2   
2           001        10                     2           001        1
3           001        3                      3           002        5
4           002        2                      3           002        3
5           002        8                      4           003        2
6           003        6                      5           004        8
7           004        23                     6           004        12
8           007        12                                ......
           ......                        
说明table1 和table2的表结构不一样，但是No是关联字段。Quantity是数值型的。
要求就是：结合table1和table2 要求得到的结果是：         No               sum(Quantity)
         001               15  (5+10+3-2-1)
         002               2   (2+8-5-3)  
         003               4   (6-2)
         004               3   (23-8-12)
         007               12
                  ......
            
谢谢！

Answer 1

select no,sum(Quantity) 
from (select Key,No,Quantity from table1
      union all
      select Key,No,Quantity from table2)
group by no;

Answer 2

没仔细看符号
select no,sum(Quantity) 
from (select Key,No,Quantity from table1
      union all
      select Key,No,0-Quantity from table2)
group by no;

Answer 3

select no,sum(Quantity)
from 
(select no,Quantity from table2  
union
select no,0-Quantity as Quantity from table2)
group by no

Answer 4

SELECT NVL(R1.NO,R2.NO) AS NO,NVL(R1.Quantity_1,0)-NVL(R2.Quantity_2,0) AS Quantity
FROM
(
SELECT NO,SUM(Quantity) Quantity_1
FROM TABLE1
GROUP BY NO
) R1
FULL OUTER JOIN
(
SELECT NO,SUM(Quantity) Quantity_2
FROM TABLE2
GROUP BY NO
) R2 ON R1.NO=R2.NO

Answer 5

select no,sum(Quantity) 
from (select Key,No,Quantity from table1
      union all
      select Key,(-1)*No,Quantity from table2)
group by no;

Answer 6

select no,sum(Quantity) 
from (select Key,No,Quantity from table1 
      union all 
      select Key,No,(-1)*Quantity from table2) 
group by no;

Answer 7

select no,sum(Quantity) 
from (select Key,No,Quantity from table1
      union 
      select Key,No, 0 - Quantity from table2)
group by no;

Answer 8

select a.no as No,a.s1-b.s2 as sum(Quantity)
from 
(select no,sum(Quantity) s1 from table1 group by no) a,
(select no,sum(Quantity) s2 from table2 group by no) b
where a.no=b.no;