来自两个表的sql sum

JK36 发表于 Dev

JK36

我在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

下蹲

一种选择是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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。