sql - 来自两个表的sql sum

Question

我在sqlite中有两个单独的表，分别称为“发票和采购”，我正在使用下面的查询来检索与项目7相关的所有发票和采购的总和。问题是发票有三个记录，并且在sql中返回了值是正确的，但是等价的购买错误，因为只有一条记录，但是返回值乘以三。

SELECT sum(invoice.invoice_net) As Sales, sum(purchase.total_order) As Purchases
FROM invoice
LEFT JOIN purchase
ON purchase.projectID=invoice.projectID
WHERE invoice.projectID=7

如何连接这两个语句，以便我正确返回数据。我知道他们每个人都很好。我已经尝试过Union，但这会将数据放在一列中。

SELECT sum(invoice.invoice_net) As Sales
FROM invoice
WHERE projectID=7

SELECT sum(purchase.order_total) As Purchases
FROM purchase
WHERE projectID=7

Answer 1

一种选择是使用子查询sum结果，然后执行outer join：

SELECT invoice.Sales, purchase.Purchases
FROM (
    SELECT sum(invoice.invoice_net) As Sales, projectID
    FROM invoice
    GROUP BY projectID
) invoice LEFT JOIN (
    SELECT sum(total_order) As Purchases, projectID
    FROM purchase 
    GROUP BY projectID
) purchase ON purchase.projectID=invoice.projectID
WHERE invoice.projectID=7

另一个选择是使用相关子查询：

SELECT sum(i.invoice_net) As Sales,
    (SELECT sum(p.total_order) 
    FROM purchase p
    WHERE p.projectID = i.projectID) As Purchases
FROM invoice i
WHERE i.projectID=7