Question

【急！】SQL两个查询值相减

两个表
食堂库存信息表CanteenInventory(CA*,FD*,FoodQuantity,InDate*)
食堂需求信息表CanteenNeed(CA*,FD*,NeedQuantity,NeedDate*)

第一个sql查询Canteeninventory表中的食物编号，以及库存总量
select Canteeninventory.fd, sum(FoodQuantity) from Canteeninventory where CA='CA01' group by fd
如查询结果为：
FD (无列名)
FD1 49
FD2 30
FD3 14
第二个sql查询CanteenNeed表中的食物编号，以及需求量总量
select CanteenNeed.fd,needQuantity from CanteenNeed where ca='CA01' and NeedDate='2009-5-20'
如查询结果为:
FD NeedQuantity
FD1 65
FD2 42
我希望将两个查询结果相减，得到采购量，（采购量=需要量-库存量）
也就是说
最终的查询结果是
FD PurchaseQuantity
FD1 16
FD2 12

请高手帮忙咯！
谢谢下面的回答，还有一个问题~
倘若Inventory表里没有，但是Need表里有，怎么办？ 是不是要另写一个sql，做两个表的差？

Answer 1

select a.fd,(a.FoodQuantity-b.needQuantity) PurchaseQuantity
from
(select Canteeninventory.fd fd, sum(FoodQuantity) FoodQuantity from Canteeninventory where CA='CA01' group by fd) a,
(select CanteenNeed.fd fd,needQuantity from CanteenNeed where ca='CA01' and NeedDate='2009-5-20') b
where a.fd=b.fd

----------------------补充------------------
倘若Inventory表里没有，但是Need表里有，那你想怎么显示，你不说明白了怎么写？

Answer 2

--对于Inventory表里没有 的问题
select a.FD,NeedQuantity - isnull(FoodQuantity,0) 采购量
from
(
select Canteeninventory.fd, sum(FoodQuantity) from Canteeninventory where CA='CA01' group by fd
) a
right join
(
select CanteenNeed.fd,needQuantity from CanteenNeed where ca='CA01' and NeedDate='2009-5-20'
) b
on a.fd=b.fd

Answer 3

select a.fd,(a.needQuantity-isnull(b.FoodQuantity,0)) PurchaseQuantity
from
(select fd,needQuantity from CanteenNeed where ca='CA01' and NeedDate='2009-5-20') a
left join
(select fd, sum(FoodQuantity) FoodQuantity from Canteeninventory where CA='CA01' group by fd) b
on a.fd=b.fd