如果查询在MySql中返回空值，如何设置0 ?

I am not so into database and I have the following problem.

我不太喜欢数据库，我有以下问题。

I have a query like this:

我有这样一个问题:

SELECT sum(intervento.IMP_IND_POS_AFF_MIN) 
FROM TID023_INTERVENTO intervento
INNER JOIN TID018_ENTEBENEFICIARIO enteBeneficiario 
   ON(enteBeneficiario.COD_ENT = intervento.COD_ENT)
INNER JOIN anagrafiche.TPG1029_PROVNUOIST provNuovIst 
   ON (provNuovIst.COD_PRV_NIS = enteBeneficiario.COD_PRV_NIS)
WHERE intervento.COD_TIP_BAN=1 AND intervento.IMP_IND_POS_AFF_MIN is not null;

This query works fine but, as you can see, it doesn't retrieve a set of rows but return a number that is obtained by the sum() function.

这个查询可以正常工作，但是，如您所见，它不检索一组行，而是返回sum()函数获得的数字。

This number in some case could be null, in this case I don't want that this query return null (because it creates problem in my application) but in this case have to be returned the numeric value 0.

在某些情况下，这个数字可能是null，在这种情况下，我不希望这个查询返回null(因为它在我的应用程序中产生了问题)，但是在这种情况下，必须返回数值0。

I know that MySql provide an ifnull() function to do something like this, here the reference:

我知道MySql提供了一个ifnull()函数来做这样的事情，这里的引用是:

http://www.w3schools.com/sql/sql_isnull.asp

but in this exalmpe it is used on single field on a table. How can I do something like this on my query output?

但是在这个过程中，它被用在桌子上的单个字段上。如何对查询输出执行类似的操作?

2 个解决方案

#1

Use:

使用:

SELECT IFNULL(SUM(intervento.IMP_IND_POS_AFF_MIN), 0)

This means IFNULL can be applied to the value returned by SUM in the same way it is applied to a table field.

这意味着IFNULL可以应用到由SUM返回的值，就像它应用到表字段一样。

#2

You can still use IFFNULL():

您仍然可以使用IFFNULL():

SELECT IFNULL(SUM(...), 0 ) FROM ...

参见:如果字段在MySQL中为空，则返回0

#1