左联接，求和和分组 [英] Left join, sum and count group by

问题描述

我有3张桌子:商品，商店和图片.在第一个表中存储商品标题.第二部分-不同存货中的商品余额，第三部分-指向商品图片的链接.因此，商品与商店和图片之间存在一对多的联系. 现在，我需要通过一个查询获得一张包含剩余库存总和的图片数量的商品清单. 我是这样做的:

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:

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`

一切正常，而好有0张或1张图片.但是当它有2时-储藏空间翻倍，我不明白为什么. 怎么了?

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?

推荐答案

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

Your issue is that when you have two (or more) store rows and two (or more) pics rows for a single goods row, you end up with the product of all the combinations of rows.

要解决此问题，请在加入之前进行汇总:

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) 

这篇关于左联接，求和和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！