当where子句中的术语不在数据库中时，如何从MySQL数据库返回0? [英] How do I Return 0 From a MySQL db When the Term in the Where Clause is Not in the database?

问题描述

如果WHERE子句中的邻居不存在，如何使我的mysql数据库返回0?因此，在下面的示例中，旧城区不在数据库中.我希望数据库返回0个事件，而不是一个空结果.

How do I get my mysql database to return 0 if the neighborhood in the WHERE clause doesn't exist? So in the example below, Old Town is not in the database. I'd like the database to return 0 incidents instead of an empty result.

SELECT incidents, 
       neighborhoods 
 FROM `myTable` 
WHERE neighborhoods ='Old Town'

我也尝试过

SELECT IFNULL(incidents,0), 
       IFNULL(neighborhoods,0) 
  FROM `myTable` 
 WHERE neighborhoods ='Old Town'

任何建议将不胜感激.

推荐答案

我对您的问题的看法是构造一个您希望找到的neighborhoods值的派生表，并LEFT JOIN构造到实际的表中:

My take on your issue is to construct a derived table of the neighborhoods values you hope to find, and LEFT JOIN to the actual table:

   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

这篇关于当where子句中的术语不在数据库中时，如何从MySQL数据库返回0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！