如果没有找到记录，Mysql 计数返回零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1528688/
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
Mysql count return Zero if no record found
提问by David King
I have a two tables.
我有两张桌子。
cities - id_city, city_name
properties - id_property, id_city, property_name
I want to display cities.city_nameand next to it [properties.count(id_city)]
我想cities.city_name在它旁边显示[properties.count(id_city)]
How do I make a query that still returns zero if no records are found instead of NULL, so that I get results like this:
如果没有找到记录而不是NULL，我如何进行仍然返回零的查询，以便我得到如下结果：
London [123]
New York [0]
Berlin [11]
where "New York" is [0], not NULLand not 1?
其中“纽约”是 [0]，NULL而不是 1？
回答by ChssPly76
Use an outer join:
使用外连接：
select cities.city_name, count(properties.id_city)
from cities left join properties on cities.id_city = properties.id_city
group by 1
回答by Shawn
I think the following will do it for you, though I haven't tested it. The trick is to get the property counts in one table, and then to left join that table to the cities table, converting NULLs to 0s using the IFNULLfunction.
我认为以下内容可以为您完成，尽管我还没有测试过。诀窍是在一个表中获取属性计数，然后将该表左连接到城市表，使用IFNULL函数将 NULL 转换为 0 。
SELECT city_name, IFNULL(property_count, 0)
FROM cities
LEFT JOIN
(SELECT id_city, count(*) as property_count
FROM properties
GROUP BY id_city) city_properties
USING (id_city);
回答by Larry Lustig
The query:
查询：
SELECT cities.*, COUNT(properties.id_city) as num
FROM cities
LEFT JOIN properties on cities.id_city=properties.id_city
GROUP BY cities.id_city
should return a 0 count where you want it, although I'm not 100% certain it works that way in MySQL.
应该在你想要的地方返回一个 0 计数，虽然我不是 100% 确定它在 MySQL 中以这种方式工作。
回答by Shawn
Simple. Use "(Count(*) + 0)". Any null plus 0 will return a number.
简单的。使用“（计数（*）+ 0）”。任何空值加 0 都将返回一个数字。
