左连接，按组求和和计数

Question

我有三个表：商品、商店和图片。第一个表中存储了商品的标题。第二个表中存储了不同库存中商品的余额，第三个表中存储了指向商品图片的链接。因此，商品与商店和图片之间存在一对多的关系。 现在，我需要通过一个查询获取商品列表，并计算库存余额总和和图片数量。 我是这样做的：

SELECT good.id, good.title, sum(store.rest) AS storerest, count(pics.id) AS picscount 
FROM goods 
LEFT JOIN store ON (goods.id = store.goodid) 
LEFT JOIN pics ON (goods.id = pics.goodid) 
GROUP BY goods.id`

当good有0或1张图片时一切都看起来正常。但是当它有2个——存储空间翻倍时，我就无法理解为什么会这样。出了什么问题？

- setrul

---

如果您删除GROUP BY子句，您将看到为什么sum（store.rest）不是您所期望的。 - Alex

如果在表pics中，特定的pics.goodid（行）存在多个行，则看起来MySQL会为每个pics.goodid行创建附加虚拟行。并插入来自goods和store表的值，其中pics.goodid=store.goodid=goods.id。并且使用sum(store.rest)简单地汇总所有行（包括虚假行）。这里有一个示例http://sqlfiddle.com/#!9/0caa01/1可以帮助理解发生了什么。对我来说不太容易理解... - user2360831

Answer 1

您的问题是，当您有两个或更多store行和两个或更多pics行用于单个goods行时，您最终会得到所有行组合的乘积。

要解决此问题，请在连接之前进行聚合：

SELECT 
  good.id, 
  good.title, 
  IFNULL(s.storerest, 0) AS storerest, 
  IFNULL(p.picscount, 0) AS picscount
FROM goods 
LEFT JOIN (
  SELECT goodid, sum(rest) AS storerest
  FROM store
  GROUP BY goodid
) s ON (goods.id = s.goodid) 
LEFT JOIN (
  SELECT goodid, count(id) AS picscount
  FROM pics
  GROUP BY goodid
) p ON (goods.id = p.goodid) 

- Michael Fredrickson

Answer 2

你正在将“goods”表与另外两个表连接，这两个表分别与“goods”表存在一对多的关系。当它们被联接时，会产生行的组合 - 如果有2张图片，则存储项目将列出两次。

如果你首先计算子表的统计信息，然后将它们联接，并在计算唯一项时使用不同的计数方法，那么最简单的解决方法就是使用去重计数。例如，你应该查询如下:

SELECT good.id, good.title, sum_rest AS storerest, count(distinct pics.id) AS picscount 
FROM goods 
LEFT JOIN (select goodid, sum(rest) as sum_rest from store) s ON (goods.id = s.goodid) 
LEFT JOIN pics ON (goods.id = pics.goodid) 
GROUP BY goods.id

- Zoltan Fedor

---

请问，使用LEFT JOIN时，如何在子查询中只选择所需的列而不是整个表中的所有列？ - user2360831

@user2360831 - 是的，正如你在我的上面回答中所看到的，store 子查询仅使用 goodid 和 rest 字段，并且不关心 store 表的任何其他列（如果有的话）。 - Zoltan Fedor

Answer 3

首先考虑连接的大小。如果一个商品有两张图片，那么这个商品的行数将增加一倍。实际上，行会被复制，但是图片部分不同。因此，store.rest的总和会增加一倍。如果有三张图片，则输出会增加三倍。

- Udo Klein