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优化可以解决这个问题。你必须进行基准测试。
相关文章