SQL Server关于SQL语句中SUM函数返回NULL的解决办法

SUM
是SQL语句被之标准求和函数,如果没有符合条件的记录,那么SUM函数会回到NULL。

不过大部分情景下,我们期待如果无符合条件记录的情形下,我们要她回到回0,而休是NULL,那么我们可采取像下面的法门来拍卖:

SELECT COALESCE(SUM(name),0) FROM person WHERE id > 0

 

履了,这生便无须费事去处理回来结果是否也NULL的事态了。

COALESCE
函数的意是返参数列表中首先独为空的价,该措施允许传入多个参数,该函数也是SQL中之专业函数。

接下来查了翻关于对NULL值的论断。地址:http://www.w3schools.com/sql/sql_isnull.asp

SQL Server / MS Access

 

1 SELECT
ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
2  FROM
Products

Oracle

Oracle does not have an ISNULL() function. However, we can use the NVL()
function to achieve the same result:

1 SELECT
ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
2  FROM
Products

MySQL

MySQL does have an ISNULL() function. However, it works a little bit
different from Microsoft’s ISNULL() function.

In MySQL we can use the IFNULL() function, like this:

1 SELECT
ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
2  FROM
Products

or we can use the COALESCE() function, like this:

 

1 SELECT
ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
2  FROM
Products

相关文章