MySQL 条件 NOT NULL case SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4821031/
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
Conditional NOT NULL case SQL
提问by Spencer
I am trying to calculate a field and I want it to behave differently depending on if one of the columns happens to be null. I am using MySQL
我正在尝试计算一个字段，我希望它根据其中一列是否为空而表现出不同的行为。我正在使用 MySQL
CASE
WHEN reply.replies <> NULL THEN
24/((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(qcr.LAST_MOD_TIME)+3600)/3600)*(ces.EXPERT_SCORE+2.5*scs.SIMILARITY)*(EXP(-reply.replies))
ELSE 1
END as ANSWER_SCORE
Is this the right syntax?
这是正确的语法吗？
回答by Joe Stefanelli
You need to have when reply.replies IS NOT NULL
你需要有 when reply.replies IS NOT NULL
NULL is a special case in SQL and cannot be compared with = or <> operators. IS NULL and IS NOT NULL are used instead.
NULL 是 SQL 中的一种特殊情况，不能与 = 或 <> 运算符进行比较。改为使用 IS NULL 和 IS NOT NULL。
回答by Tomalak
case when reply.replies IS NOT NULL ...
You can't compare NULL with the regular (arithmetic) comparison operators. Any arithmetic comparison to NULL will return NULL, even NULL = NULLor NULL <> NULLwill yield NULL.
您不能将 NULL 与常规（算术）比较运算符进行比较。任何与 NULL 的算术比较都将返回 NULL，甚至NULL = NULL或NULL <> NULL将产生 NULL。
Use ISor IS NOTinstead.
使用IS或IS NOT代替。
回答by RichardTheKiwi
You don't need a case statement for this.
Use the IFNULL function
为此，您不需要 case 语句。
使用IFNULL 函数
IFNULL(24/((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(qcr.LAST_MOD_TIME)+3600)/3600)
*(ces.EXPERT_SCORE+2.5*scs.SIMILARITY)*(EXP(-reply.replies)), 1) as ANSWER_SCORE
If reply.replies is null, the expression is shortcut to NULL
IFNULL then takes the 2nd parameter (1) and gives that as a result when it happens.
如果reply.replys 为空，则表达式是NULL 的快捷方式
IFNULL 然后采用第二个参数(1) 并在它发生时给出结果。
For other cases where you do need to compare to NULL, this will help youto work with MySQL.
对于您确实需要与 NULL 进行比较的其他情况，这将有助于您使用 MySQL。
回答by Wainer De Paula
You can make a CASE Statement Checking Null
您可以使 CASE 语句检查为空
SELECT MAX(id+1),
IF(MAX(id+1) IS NULL, 1, MAX(id+1))
AS id
FROM `table_name`;
