MySQL 如果计数值为空，则将其设置为零 - sql select 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23618616/
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
if count value is null set it to zero - sql select statement
提问by user756659
Can't seem to get this working. If the count is null I want to set it to zero... else set it to the count. I am adding multiple counts in another part of my code so I cannot have null values when I do this.
似乎无法让这个工作。如果计数为空，我想将其设置为零...否则将其设置为计数。我在代码的另一部分添加了多个计数，因此在执行此操作时不能有空值。
$table = " ...
$table = " ...
LEFT JOIN
(SELECT user_id, IF(count(user_id) = '(null)',0,count(user_id)) as t1count
FROM screenshot_logs
GROUP BY user_id) as t_screenshots
on t_screenshots.user_id = users.user_id
...
";
";
回答by spencer7593
In the outer query, you can replace a NULLwith a zero using the IFNULL()function, e.g.
在外部查询中，您可以NULL使用IFNULL()函数将 a 替换为零，例如
SELECT ...
, IFNULL(v.t1count,0) AS t1count
FROM ...
LEFT
JOIN ( SELECT ... AS t1count
...
) v
ON ...
The NULL you are getting returned by the outer query isn't from the inline view query. The NULL is a result of "no match" being found by the LEFT [OUTER] JOINoperation.
外部查询返回的 NULL 不是来自内联视图查询。NULL 是LEFT [OUTER] JOIN操作找到“不匹配”的结果。
If you are referencing v.t1countin other expressions in the outer query, you can replace those references with NULLIF(v.t1count,0)as well.
如果您v.t1count在外部查询中的其他表达式中引用，您也可以将这些引用替换NULLIF(v.t1count,0)为。
回答by dhirschl
The aggregate COUNT() will always return a value.
聚合 COUNT() 将始终返回一个值。
Reference: Does COUNT(*) always return a result?
回答by Tripp Kinetics
You don't want to use =in there because null is !=to everything. Try the ifNull()function.
你不想=在那里使用，因为 null 是!=一切。试试这个ifNull()功能。
http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html#function_ifnull
http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html#function_ifnull
