在具有三个表的查询中使用Join、Sum和Group by

Question

我有三个表：

**OBTable**
Product           obquantity       obrate        obTotalAmmount
Matadoor Pen      100              8             800
Matadoor Pen      1000             4             4000
**PurchaseTable**
pProduct        Pquantity    pRate   SaleRate     pTotalAmmount
Matadoor Pen    150          4       5            600
Matadoor Pen    400          8       10           3200
Matadoor Pen    1500         9       10           13500
**SaleTable**
sProduct             sQuantity    sRate        sTotalAmmount
Matadoor Pen         100          10           1000
Matadoor Pen         350          10           3500
Matadoor Pen         1350         10           13500

我的查询：

选择产品SUM(obQuantity(AS obQuantity，SUM(obTotalAmount(/SUM(obQuantity(AS obRate，SUM(obTotalAmount(AS obTotalAMount，

pProduct，SUM(pQuantity(AS pQuantity，SUM(pTotalAmount(/SUM(pQuantity(AS pRate，SUM(pTotalAmount(AS pTotalAMount，

s产品，SUM(sQuantity(AS sQuantity，SUM(s总量(/SUM(s数量(AS s费率，SUM(sTotalAmount(作为sTotalAMount，

求和(obQuantity(+求和(pquantity(-求和(squantity(为obpsQTY，

(总和(obTotalAmmount(+总和(pTotalAmount(-总和(sTotalAmount

(求和(obTotalAmmount(+求和(pTotalAmount((-求和(sTotalAmmount(作为obpstotal

来自OBTable

左联接OBTable.Product=PurchaseTable.Product上的PurchaseTable左联接PurchaseTable.pProduct=Saletable.sProduct 上的Saletable

分组BY OBTable.Product，PurchaseTable.pProduct，Saletable.sProduct

它执行的答案不正确。Plz帮助我并给出解决它的提示。

Answer 1

您可以尝试以下代码：

Select Product,
Sum(obQuantity) As obQuantity,
(SUM(obTotalAmmount)/Sum(obQuantity)) As obRate,
SUM(obTotalAmmount) as obTotalAmmount,
pQuantity,pRate,pTotalAmmount,
sQuantity ,sRate,sTotalAmmount,
(Sum(obQuantity) +pquantity) -squantity as obpsQTY,
(((SUM(obTotalAmmount)/Sum(obQuantity)))+pRate)/2 as  obpsrate ,
((((SUM(obTotalAmmount)/Sum(obQuantity)))+pRate)/2)*((Sum(obQuantity) +pquantity) -squantity) as obpstotal 
from OBTable 
left join (
Select pProduct,Sum(pQuantity) As pQuantity ,(Sum(pTotalAmmount)/Sum(pQuantity)) As pRate,Sum(pTotalAmmount) As pTotalAmmount 
from PurchaseTable 
Group by PurchaseTable.pProduct
)
PurchaseTable on OBTable.Product=PurchaseTable.pProduct
left join
(
Select sProduct,Sum(sQuantity) As sQuantity ,(Sum(sTotalAmmount)/Sum(sQuantity)) As sRate,Sum(sTotalAmmount) As sTotalAmmount 
from SaleTable 
Group by SaleTable.sProduct 
) Saletable on PurchaseTable.pProduct=SaleTable.sProduct 
Group by OBTable.Product,PurchaseTable.pProduct,Saletable.sProduct,PurchaseTable.pQuantity,PurchaseTable.pRate,PurchaseTable.pTotalAmmount,Saletable.sQuantity,Saletable.sRate,Saletable.sTotalAmmount