MySQL 为NULL时如何用MAX函数设置0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10172174/
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 set 0 with MAX function when it is NULL?
提问by Donovant
I would like to understand how to set 0 value of the attribute when it is NULL with MAX function. For example:
我想了解如何使用 MAX 函数在属性为 NULL 时设置属性的 0 值。例如：
Name columns:
number - date
Values:
10 - 2012-04-04
11 - 2012-04-04
12 - 2012-04-04
13 - 2012-04-15
14 - 2012-06-21
1 - 2013-07-04
Number is incremental field, but it has set itself 1 when new year has come. But result of:
Number 是增量字段，但它在新年到来时将自己设置为 1。但结果：
SELECT (MAX(number)+1) number WHERE date LIKE "2014%"
is NULL and not 1 because MAX(number) is NULL and not 0
是 NULL 而不是 1 因为 MAX(number) 是 NULL 而不是 0
回答by Nanne
Well, as there is no date like 2014, you would expect null, because the maximum of nothing is actually not anyting.
好吧，因为没有像 2014 年这样的日期，所以您会期望为 null，因为没有的最大值实际上是没有任何内容的。
But do this:
但是这样做：
COALESCE(MAX(number),0)
Which means: get the first non-null thing from the next list, so if your maxis null, it'll give you 0
这意味着：从下一个列表中获取第一个非空的东西，所以如果你max是空的，它会给你0
回答by Demis Palma ツ
COALESCE works, but IFNULLseems clearer to me.
COALESCE 有效，但IFNULL对我来说似乎更清楚。
IFNULL(MAX(number), 0)
If the first expression is not NULL, IFNULL() returns the expression itself, otherwise it returns the second parameter. IFNULL() returns a numeric or string value, depending on the context in which it is used.
如果第一个表达式不为 NULL，则 IFNULL() 返回表达式本身，否则返回第二个参数。IFNULL() 返回数字或字符串值，具体取决于使用它的上下文。
