MySQL Count() 和左连接问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4983544/
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
Count() and left join problem
提问by Krt_Malta
I'm having a problem with the a query which displays a list of shops with the number of products associated with it. I've been playing around with left joins etc for quite a while now but to no avail. The tables have the following structures:
我在使用查询时遇到问题，该查询显示商店列表以及与其关联的产品数量。我一直在玩左连接等很长一段时间，但无济于事。这些表具有以下结构：
Shops table containing columns: id, name
包含列的商店表：id,name
Products table containing columns: id, name, status, shop
包含列的产品表：id, name, status,shop
The query is as follows:
查询如下：
select s.name
, p.name
, count(p.id)
from Product as p
left join Shop as s on p.shop=s.id
where p.status <> '8796107276379'
group by
s.id
I'm not getting the shops which have 0 products. How can I achieve this please?
我没有得到有 0 个产品的商店。我怎样才能做到这一点？
The underlying database is MySQL.
底层数据库是MySQL。
Thanks! Krt_Malta
谢谢！Krt_马耳他
回答by RichardTheKiwi
You need SHOP on the LEFT side, since the right side is the one that may not have data, in this case PRODUCT.
您需要左侧的 SHOP，因为右侧可能没有数据，在本例中为 PRODUCT。
Not only that, you need the WHERE condition as a LEFT-JOIN ON condition, so that it joins to products on the status condition and just discounts the product (while keeping shop) even if the status is not desired.
不仅如此，您还需要 WHERE 条件作为 LEFT-JOIN ON 条件，以便它在状态条件下加入产品，并且即使不希望状态也只是打折产品（同时保持商店）。
select s.name
, p.name
, count(p.id)
from Shop as s
left join Product as p on p.shop=s.id AND p.status <> '8796107276379'
group by
s.id, p.name
回答by ibrahim sandall?
select s.name
, p.name
, count(p.id)
from Shop as s
left join Product as p on s.id=p.shop
where p.status <> '8796107276379'
group by
s.id
回答by John McGeechan
I suffered this gotcha too and though I am not entirely sure why, placing the predicate on the jojn itself rather than the actual main query is how to solve it.
我也遇到了这个问题，虽然我不完全确定为什么，将谓词放在 jojn 本身而不是实际的主要查询上是如何解决它的。
I actually documented the whole thing, before reading this. I used a simple example with two two small tables, it explains I hope the difference, maybe it will help
在阅读本文之前，我实际上记录了整件事。我使用了一个带有两个小表的简单示例，它解释了我希望有所不同，也许会有所帮助
回答by Tommi
You need to add OR p.status IS NULLto your where clause.
您需要添加OR p.status IS NULL到您的 where 子句中。
select s.name, p.name, count(p.id)
from Shop s
left join Product p on p.shop = s.id
where (p.status <> '8796107276379' OR p.status IS NULL)
group by s.name, p.name
