Sum()返回不正确的值

我有几个表、产品、传入和传出。Out和Income有两行，这让我对发生了什么有了一些了解，因为我从查询中得到的结果是它们应该得到的结果的两倍

SELECT products.ProductName, products.StartingInventory,
        sum(incoming.NumReceived) invReceived, sum(outgoing.NumberShipped) invShipped,
        products.InventoryOnHand, products.MinimumRequired 
from incoming, products, outgoing 
where incoming.ProductId = products.id and outgoing.ProductId = products.id 
group by products.id

有问题的两个值分别是invRecept和invShipping。这是传入的表格：

| id  SupplierID ProductID NumReceived PurchaseDate |
| 1   1          1         6           2018-02-01   |
| 2   1          1         7           2017-05-09   |

和传出表格

|id First    Middle    Last        ProductId NumberShipped OrderDate |
|1  Dan      Smith     Agent       1         6             2018-02-01|
|2  Bethany  Richards  Richardson  1         15            2018-04-20|

结果为invReceied：26和invShipping 36，但应为13和18。

---

解决方案

删除group by和聚合函数(即sum)揭示了问题。

sqlite> SELECT products.ProductName, products.StartingInventory,
   ...>         incoming.NumReceived invReceived, outgoing.NumberShipped invShipped,
   ...>         products.InventoryOnHand, products.MinimumRequired 
   ...> from incoming, products, outgoing 
   ...> where incoming.ProductId = products.id and outgoing.ProductId = products.id 
   ...> 
   ...> ;
ProductName  StartingInventory  invReceived  invShipped  InventoryOnHand  MinimumRequired
-----------  -----------------  -----------  ----------  ---------------  ---------------
Dell         290                6            3           300              10             
Dell         290                7            3           300              10             
Dell         290                6            15          300              10             
Dell         290                7            15          300              10    

(我使用的是SQLite，但应该与MySQL没有区别。)

行被计算两次。只需选择ID，我们就可以更清楚地看到问题。

sqlite> SELECT products.id, incoming.id, outgoing.id
   ...> from incoming, products, outgoing 
   ...> where incoming.ProductId = products.id and outgoing.ProductId = products.id 
   ...> ;
id          id          id        
----------  ----------  ----------
1           1           1         
1           2           1         
1           1           2         
1           2           2         

---

有几种方法可以解决这个问题。One is from @JerryJermiah in the comments。

SELECT products.id, 
    (select sum(incoming.NumReceived)
     from incoming
     where incoming.productid = products.id),
    (select sum(outgoing.NumberShipped)
     from outgoing
     where outgoing.productid = products.id)
from products;

这将获取每个产品一次，然后对每个产品执行子选择，以获取NumReceired和NumberShipping。

您也可以执行类似的操作，但对子查询执行联接。

select p.id, ig.NumReceived, og.NumShipped
from products p
join (
    select productid, sum(NumReceived) as NumReceived
    from incoming
    group by productid
) as ig on p.id = ig.productid
join (
    select productid, sum(NumberShipped) as NumShipped
    from outgoing
    group by productid
) as og on p.id = og.productid

这样可能更快，因为SQL将只需为每个产品执行三个查询，而不是两个。或者，也许SQL优化可以解决这个问题。你必须进行基准测试。