将Null从SQL更改为0

Question

这个问题在这个网站上被问过几次，但是没有一个解决方案对我有效。

我正在查询中运行一个查询：

 //this query is to count variables
$sql = "SELECT variabletype, COUNT(variabletype) AS value_occurrence FROM variable GROUP BY variabletype ORDER BY value_occurrence DESC";
$vars_query = mysqli_query($con,$sql) or die(mysqli_error($con));
$pos = array();

while($vars = mysqli_fetch_array($vars_query, MYSQLI_ASSOC))
    {

  //each variable has many different values, some positive, some negative, in this query I am trying to understand of the values found, how many of them are positive and pushing the result into an array. 
 $times_positive_qry = mysqli_query($con, "SELECT variable.variabletype, COUNT(value.valueid) AS positive_occurrence FROM variable INNER JOIN value On variable.variableid=value.variableid WHERE variable.variabletype = '" .$vars['variabletype']. "' AND value.valuelift>0.00 AND value.valuesignificant=1 GROUP BY variable.variabletype ORDER BY positive_occurrence DESC");
 $times_positive = mysqli_fetch_array($times_positive_qry, MYSQLI_ASSOC);
 $pos[] = $times_positive['positive_occurrence'];

}

我的结果是：

var s1 = ["10","3","2","3","4","5","3","3","2","1",null,"2","2",null,"4","3","3","2","2","2","1","1","1",null,"1",null,null,null,"1","1",null,"1","1",null,"3",null,"1","1","1",null]

您将注意到，在某些结果中，返回的是null，这意味着特定变量没有正值。不管怎样，我可以用0来代替零吗？我试过IFNULL和COALESCE，但它们都不起作用。

最终，我需要空值为0，因为我正在绘制数组，如果我有空值，图形将不会显示。

有什么想法吗？

Answer 1

使用IFNULL函数

IFNULL(your_column,0)

从你的评论来看，你做的coalesce是错误的。应该是下面的样子。因为如果valueid是null，那么COUNT(value.valueid)无论如何都是0(尝试一个示例来查看类似于select count(null) as total)。

SELECT IFNULL(variable.variabletype,0), <-- Change Here
COUNT(value.valueid) AS positive_occurrence
FROM variable 
INNER JOIN value 
On 
variable.variableid=value.variableid 
WHERE variable.variabletype = '" .$vars['variabletype']. "' 
AND 
value.valuelift>0.00 
AND 
value.valuesignificant=1
GROUP BY variable.variabletype 
ORDER BY positive_occurrence DESC

Answer 2

而不是

"SELECT variable.variabletype, COUNT(value.valueid)..."

"SELECT variable.variabletype, isnull(COUNT(value.valueid),0)"

Answer 3

尝试isnull函数

(YOUR_VAR，0)

http://technet.microsoft.com/en-us/library/ms184325.aspx