如何获得每个唯一“列”的 SUM()？

Question

我有这些 SQL 表

“Supplier

 SUPPLIERID | SUPPLIERNAME
 1          | sup1
 2          | sup2
 3          | sup3

Sold

ITEMID | SOLD | SUPPLIER ID | DATE
pen    | 10   | 1           | 2011-10-21
pen    | 5    | 1           | 2011-10-22
pen    | 5    | 1           | 2011-10-23
pen    | 20   | 3           | 2011-10-24

Bought

 ITEMID | BOUGHT | SUPPLIER ID | DATE
 pen    | 20     | 1           | 2011-9-21
 pen    | 5      | 3           | 2011-9-24

”，我目前正在使用此脚本，但它似乎不起作用，因为销售和购买的 SUM() 增加了三倍。我怀疑原因是因为有 3 行已售出，但只有 2 行已购买。知道如何解决这个问题吗？

   SELECT suppliername, SUM(sold) as sold, SUM(bought) AS bought
     FROM sold s
LEFT JOIN supplier sp
       ON s.supplierid = sp.supplierid
LEFT JOIN bought b
       ON sp.supplierid = b.supplierid
 GROUP BY suppliername

Answer 1

SELECT suppliername, Sold, Bought
FROM Supplier sp
LEFT JOIN (SELECT SupplierId, SUM(Sold) Sold
           FROM sold
           GROUP BY SupplierId) s
ON sp.SupplierId = s.SupplierId
LEFT JOIN (SELECT SupplierId, SUM(bought) bought
           FROM bought
           GROUP BY SupplierId) b
ON sp.SupplierId = b.SupplierId

基本上，我正在做的是分别计算每个供应商ID的销售量和购买量（在LEFT JOIN中），所以我总是有销售总额和购买总额。我在 LEFT JOIN 中使用的查询称为派生表，因为它们的作用类似于一般查询中的表。希望这可以帮助您更清楚地了解我所做的事情。

Answer 2

根据给定的数据，我希望看到供应商 1 的结果增加了两倍，供应商 2 根本没有返回任何内容，供应商 3 的数量也正确。这是因为您隐式地将每个供应商的每个结果从已售出链接到每个结果每个供应商都买了。

相反，请尝试：

select supplierid, max(suppliername) suppliername, sum(bought) bought, sum(sold) sold
from (
    select supplierid, suppliername, 0 bought, 0 sold from supplier union all
    select supplierid, '' suppliername, bought, 0 sold from bought union all
    select supplierid, '' suppliername, 0 bought, sold from sold) ilv
group by supplierid;