如果WHERE子句中的邻域不存在,如何让我的mysql数据库返回0？因此,在下面的示例中,Old Town不在数据库中.我希望数据库返回0个事件而不是空结果.
SELECT incidents,neighborhoods
FROM `myTable`
WHERE neighborhoods ='Old Town'
我也试过了
SELECT IFNULL(incidents,0),IFNULL(neighborhoods,0)
FROM `myTable`
WHERE neighborhoods ='Old Town'
任何建议都会非常感激.
最佳答案
我对你的问题的看法是构建一个你希望找到的邻域值的派生表,并将LEFT JOIN加到实际的表中：
SELECT x.neighborhoods,COALESCE(mt.incidents,0) AS incidents
FROM (SELECT 'Old Town' AS neighborhoods
FROM DUAL
UNION ALL
SELECT 'New Town'
FROM DUAL) x
LEFT JOIN MYTABLE mt ON mt.neighborhoods = x.neighborhoods