当任一字段为NULL时，Mysql的字段总和不起作用

问题描述：

Hi i have this code in php to update a balance field in $table5. Now my problem is that when one of the two fields i.e. add or sub is NULL then this does not return balance.

    $fetch_balance = mysql_query("SELECT (SUM (IFNULL(`add`,0))-(SUM (IFNULL(`sub`,0)))          AS `bal` FROM `".$table5."` ");

Please help.

您好我在PHP中使用此代码来更新$ table5中的余额字段。现在我的问题是当两个字段之一，即add code>或 sub code>为NULL时，这不会返回余额。 p>

  $ fetch_balance = mysql_query（“SELECT（SUM（IFNULL（`add`，0）） - （SUM（IFNULL（`sub`，0）））AS`bal` FROM`”。$ table5。“`”）  ; 
  code>  pre> 
 
 请帮忙。 p> 
  div>

答

I tried your query, and it worked just fine, after some slight tweak: http://sqlfiddle.com/#!2/a6220/4

The final query should look like this: SELECT (SUM(IFNULL(add,0)))-(SUM(IFNULL(sub,0))) AS bal FROM ".$table5."

You forgot to add a ), and also, you had spaces after SUM(). SUM is a function, and should therefore not have any spaces before the ().

答

Your query simplified to be syntactically correct:

SELECT SUM(IFNULL(`add`, 0) - SUM(IFNULL(`sub`, 0) AS `bal`
FROM `".$table5."`;

Assuming the table has rows, then this will return a single, non-NULL value for bal. If the table has no rows, then it will return NULL.

You probably intend this logic:

select coalesce(sum(`add`), 0) - coalesce(sum(`sub`), 0) as bal
from . . .;

This does the replacement only if the entire sum() is NULL. The sum() aggregation function treats NULL values as 0.

Note: I replaced the ifnull() function with the ANSI standard equivalent coalesce().

答

create table foo (bar1 integer, bar2 integer);

insert into foo (bar1, bar2) values (null, 1);
insert into foo (bar1, bar2) values (1, null);

select sum(coalesce(bar1, 0) + coalesce(bar2, 0)) from foo;
>2

select sum(bar1 + bar2) from foo;
>null

EDIT hint: try something like this:

SELECT SUM ( IFNULL(`add`,0) - IFNULL(`sub`,0) )

当任一字段为NULL时，Mysql的字段总和不起作用

相关推荐