求两表求和的SQL语句

Question

有两张表Table1，Table2。table1数据如下：
ID         product          amount
----------------------------------
1          A                 10
2          A                 12
3          B                 5
4          B                 11
5          A                 3
table2数据如下：
ID         product          amount
----------------------------------
1          A                 5
2          A                 10
3          B                 5
我想求出table1中A、B的总数，再减去table2中A、B的总数，然后生成新表
ID         product          amount
----------------------------------
1          A                 10
2          A                 11这个SQL怎么写？

Answer 1

Select product,sum(table1.amount) - sum(table2.amount) as amount 
from  table1 group by product

Answer 2

select a.product,(a.amount -b.amount ) from 
(select product,sum(amount) amount from table1 group by product)a
(select product,sum(amount) amount from table2 group by product)b
where a.product=b.product

Answer 3

Select product,sum(table1.amount) - sum(table2.amount) as amount 
from  table1,table2 where table1.product=table2.product  group by product我的楼上写少了点东西

Answer 4

LZ的答案是错的。应该是
a 10
b 11

Answer 5

select * from table1 where product not in (select product from table2)