MySQL 如果不存在记录，如何选择 sum -or- 0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8888390/
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
How to select sum -or- 0 if no records exist?
提问by ewok
I need to write a query that returns the sum of all values that meet a certain criteria, but the query needs to return 0 if no rows are found, rather than null. For example:
我需要编写一个查询，返回满足特定条件的所有值的总和，但如果没有找到行，查询需要返回 0，而不是 null。例如：
tab
+---------------+-----+
| descr | num |
+---------------+-----+
| hello there | 5 |
| hi there | 10 |
| hello | 10 |
| hi there! | 15 |
+---------------+-----+
This query:
这个查询：
SELECT sum(num) AS val FROM tab WHERE descr LIKE "%hello%";
should, and does, return 15. However:
应该并且确实返回15。然而：
SELECT sum(num) AS val FROM tab WHERE descr LIKE "%greetings%";
should return 0, but does return null.
应该返回0，但确实返回null。
Can someone explain if this is possible?
有人可以解释一下这是否可能吗？
回答by Mike Christensen
How about:
怎么样：
SELECT COALESCE(sum(num), 0) AS val FROM tab WHERE descr LIKE "%greetings%";
The COALESCEfunction basically says "return the first parameter, unless it's null in which case return the second parameter" - It's quite handy in these scenarios.
该COALESCE函数基本上说“返回的第一个参数，除非它在这种情况下，空返回第二个参数” -这是在这些情况下，会非常方便。
回答by sesser
Check the MySQL documentation for IFNULL.
SELECT SUM(IFNULL(num, 0)) as val FROM tab WHERE descr LIKE "%greetings%";
Of course, this assumes your numfield is nullable and doesn't have a default value. Another possible solution would be to set a default of 0for the num field which should solve the issue you're having.
当然，这假设您的num字段可以为空并且没有默认值。另一种可能的解决方案是0为 num 字段设置默认值，这应该可以解决您遇到的问题。
回答by Ken Williams
To do this properly, you may want to distinguish between the case where there are actual NULLresults in the data you're summing, and the case where there are no values at all to sum.
要正确执行此操作，您可能需要区分NULL正在求和的数据中有实际结果的情况和根本没有要求和的值的情况。
Suppose we have the following:
假设我们有以下内容：
mysql> select * from t;
+----------+------+
| descr | num |
+----------+------+
| hiya | 5 |
| hi there | 10 |
| yo | NULL |
+----------+------+
We would like empty sums to be zero, but sums involving NULLto be NULL. One (rather torturous) way to do that is:
我们希望空和为零，但涉及的NULL和为NULL。一种（相当痛苦的）方法是：
mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
-> SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
-> FROM t WHERE num < 'ciao')
-> AS u;
+------+
| sum |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
-> SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
-> FROM t)
-> AS u;
+------+
| sum |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
-> SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
-> FROM t WHERE descr < 'namaste')
-> AS u;
+------+
| sum |
+------+
| 15 |
+------+
1 row in set (0.00 sec)
mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
-> SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
-> FROM t WHERE descr > 'namaste')
-> AS u;
+------+
| sum |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
Perhaps there's a better way I haven't thought of.
也许有更好的方法我没有想到。
Unfortunately, the SQL standard defines SUMto be null when no elements are summed, and MySQL has no choice but to follow that standard.
不幸的是，SQL 标准定义SUM为 null 当没有元素相加时，MySQL别无选择，只能遵循该标准。
回答by Thomas Kelley
This works:
这有效：
SELECT IF(SUM(num) IS NULL, 0, SUM(num)) AS val FROM tab WHERE descr LIKE "%whatever%";
IF() takes three parameters: (1) A statement, (2) the value to apply if the statement is true, and (3) the value to apply if the statement is false.
IF() 接受三个参数：(1) 一个语句，(2) 如果语句为真则应用的值，以及 (3) 如果语句为假则应用的值。
