本文介紹了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優化可以解決這個問題。你必須進行基準測試。
這篇關于Sum()返回不正確的值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
