使用聚合函数在Select查询中连接多个表

I want to join these two queries, I want to subtract SQuantity from PQuantity and SAmount from PAmount. How can I achieve this with one query.

我想加入这两个查询,我想从Pmount中减去SQuantity,从PAmount中减去SAmount。如何通过一个查询实现此目的。

SELECT        
SUM(QtyShare) AS SQuantity, SUM(QtyShare * Rate) AS SAmount,CompanyId
FROM Sales
WHERE (UserId = 1)
GROUP BY CompanyId

SELECT  SUM(Quantity) AS PQuantity, SUM(Quantity * Rate) AS PAmount,CompanyId
FROM (SELECT   UserId, CompanyId, Quantity, Rate AS Rate
       FROM  Purchase
       UNION ALL
       SELECT UserId, CompanyId, Quantity, 0 AS Rate
       FROM   Bonus
       UNION ALL
       SELECT UserId, CompanyId, Quantity, Rate AS Rate
       FROM   RightShare) AS t
WHERE (UserId = 1) GROUP BY CompanyId

1 个解决方案

#1

Try:

SELECT CompanyId,
       SUM(PQuantity) PQuantity, 
       SUM(PQuantity * PRate) PAmount,
       SUM(SQuantity) SQuantity, 
       SUM(SQuantity * sRate) SAmount
       SUM(PQuantity) - SUM(SQuantity) TQuantity, 
       SUM(PQuantity * PRate) - SUM(SQuantity * sRate) TAmount
FROM 
(SELECT UserId, CompanyId, Quantity PQuantity, Rate PRate, 0 SQuantity, 0 SRate
 FROM  Purchase
 UNION ALL
 SELECT UserId, CompanyId, Quantity PQuantity, 0 PRate, 0 SQuantity, 0 SRate
 FROM   Bonus
 UNION ALL
 SELECT UserId, CompanyId, Quantity PQuantity, Rate PRate, 0 SQuantity, 0 SRate
 FROM   RightShare
 UNION ALL
 SELECT UserId, CompanyId, 0 PQuantity, 0 PRate, QtyShare SQuantity, Rate SRate
 FROM   Sales) sq
WHERE (UserId = 1) 
GROUP BY CompanyId