当Where子句中的术语不在数据库中时，如何从MySQL数据库返回0？

发布于 2024-08-23 22:13:37 字数 373 浏览 2 评论 0原文

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

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

我也尝试过

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

任何建议将非常感激。

原文

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'

I also tried

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

Any suggestions would be really appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

涫野音 2024-08-30 22:13:37

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

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

回复收藏 0 原文

零時差 2024-08-30 22:13:37

我对您的问题的看法是构建您希望找到的“neighborhoods”值的派生表，并“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

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

回复收藏 0 原文

笑看君怀她人 2024-08-30 22:13:37

您可以将 COUNT(*) 添加到选择部分，这将为您提供结果集中的行数。只要您只想读取一行，它就会为您提供 0 或 1。

SELECT COUNT(*), Foo, Bar FROM Bla WHERE false;
+----------+-----+-----+
| COUNT(*) | Foo | Bar |
+----------+-----+-----+
|        0 | NULL | NULL |
+----------+-----+-----+

SELECT COUNT(*), Foo, Bar FROM Bla WHERE Bar = 0;
+----------+------------+-----+
| COUNT(*) | Foo        | Bar |
+----------+------------+-----+
|        1 | 2147483647 |   0 |
+----------+------------+-----+

但是，如果您要获取多于一行，则可能会失败，因为您只能从结果集中获取一行。

SELECT COUNT(*), Foo, Bar FROM Bla WHERE Bar >= 0;
+----------+-----+-----+
| COUNT(*) | Foo | Bar |
+----------+-----+-----+
|        7 |   3 |   6 |
+----------+-----+-----+

（并且不要忘记为 COUNT(*) 列使用别名）

You can add COUNT(*) into the select part, which gives you the amount of rows in the result set. As long you want to read only one row it gives you either 0 or 1.

SELECT COUNT(*), Foo, Bar FROM Bla WHERE false;
+----------+-----+-----+
| COUNT(*) | Foo | Bar |
+----------+-----+-----+
|        0 | NULL | NULL |
+----------+-----+-----+

SELECT COUNT(*), Foo, Bar FROM Bla WHERE Bar = 0;
+----------+------------+-----+
| COUNT(*) | Foo        | Bar |
+----------+------------+-----+
|        1 | 2147483647 |   0 |
+----------+------------+-----+

But if you would get more than one row it may fail as you get only one row from the result set.

SELECT COUNT(*), Foo, Bar FROM Bla WHERE Bar >= 0;
+----------+-----+-----+
| COUNT(*) | Foo | Bar |
+----------+-----+-----+
|        7 |   3 |   6 |
+----------+-----+-----+

(And don't forget to use an alias for the COUNT(*) column)

回复收藏 0 原文