MySQL 左连接、求和和计数分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14967160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left join, sum and count group by
提问by setrul
I have 3 tables: goods, store and pics. In the first table goods titles are stored. In the second - balance of goods in different stocks, in the third - links to goods pictures. So goods has one-to-many connection with store and pics. Now, I need to get a list of goods with sum of stock rests and count of pictures by one query. I did it like this:
我有 3 张桌子：商品、商店和图片。在第一个表中存储商品名称。在第二个 - 不同库存中的商品余额，在第三个 - 商品图片链接。所以商品与商店和图片是一对多的联系。现在，我需要通过一个查询来获得一个包含库存总和和图片数量的商品列表。我是这样做的：
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`
All seems ok while good has 0 or 1 picture. But when it has 2 - storerest doubles, and I can't understand why. What's wrong?
一切似乎都很好，而好的有 0 或 1 张图片。但是当它有 2 个时 - storerest 加倍，我不明白为什么。怎么了？
回答by Michael Fredrickson
Your issue is that when you have two (or more) storerows and two (or more) picsrows for a single goodsrow, you end up with the product of all the combinations of rows.
您的问题是，当您有两store行（或更多）行和一行（或更多）pics行时goods，您最终会得到所有行组合的乘积。
To fix this, do your aggregation before joining:
要解决此问题，请在加入之前进行聚合：
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)
回答by Udo Klein
First consider the size of the join. If there are two pictures for one good then there will be twice as many rows for this good. Actually the rows will be duplicated but for the picture part. Hence the sum of the store.rest will pick up everything twice. With three pictures you would get three times the output.
首先考虑连接的大小。如果一种商品有两张图片，那么该商品的行数将是其两倍。实际上，这些行将被复制，但对于图片部分。因此， store.rest 的总和将提取所有内容两次。使用三张图片，您将获得三倍的输出。
回答by Zoltan Fedor
You are joining together table 'goods' with two other tables, where these two other tables have a one-to-many relations to the 'goods' table. When they are joined, a combination of rows will results - so if there are 2 pics then store items are listed twice.
您将表 'goods' 与另外两个表连接在一起，另外两个表与 'goods' 表具有一对多的关系。当它们连接时，将产生行的组合 - 因此，如果有 2 张图片，则商店项目将列出两次。
The easiest way to solve this if you first calculate the stats of the sub-tables and then you join them and use distinct counting when counting unique items, so for example you query should really be:
如果您首先计算子表的统计信息，然后加入它们并在计算唯一项目时使用不同的计数，那么解决这个问题的最简单方法是，例如，您的查询实际上应该是：
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
