我想取得一张单子下所有物资的价格合计及成本合计:select A.*,(select sum(C1.Price*B1.FoodNum) as AllPrice from TFeastList B1,TMenu C1 where B1.FID = A.ID and B1.MenuID = C1.ID ),(select sum(C1.Cost*B1.FoodNum) as AllCost from TFeastList B1,TMenu C1 where B1.FID = A.ID and B1.MenuID = C1.ID ) from TFeast A where A.ID = @ID这样做,分别取得价格和成本,我感觉不是很好的。select A.*,(select sum(C1.Price*B1.FoodNum) as AllPrice,sum(C1.Cost*B1.FoodNum) as AllCost from TFeastList B1,TMenu C1 where B1.FID = A.ID and B1.MenuID = C1.ID ) from TFeast A where A.ID = @ID改成这样又有错。哪位高手有其它解决方案啊?谢谢
select A.*,
(select sum(C1.Price*B1.FoodNum) as AllPrice,
sum(C1.Cost*B1.FoodNum) as AllCost
from TFeastList B1,TMenu C1
where B1.FID = A.ID and B1.MenuID = C1.ID )
from TFeast A where A.ID = @ID--变量没有赋值
改写一下语句1
select A.*,AllPrice,AllCost
from
(select B1.FID,sum(C1.Price*B1.FoodNum) as AllPrice,
sum(C1.Cost*B1.FoodNum) as AllCost
from TFeastList B1,TMenu C1
where B1.MenuID = C1.ID group by B1.FID )test,
,TFeast A
where A.ID = test.FID
from TFeastList B1,TMenu C1 ,TFeast A
where B1.FID = A.ID and B1.MenuID = C1.ID
and A.ID = @ID--条件
group by A.ID,....把A表的所有列作为 group by
from TFeast A , TFeastList B1 , TMenu C1
where B1.FID = A.ID and B1.MenuID = C1.ID