从 SQL 函数获取单个值时，如何将 NULL 更改为 0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1005036/
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
How can I change NULL to 0 when getting a single value from a SQL function?
提问by Matt
I have a query that counts the price of all items between two dates. Here is the select statement:
我有一个查询，计算两个日期之间所有项目的价格。这是选择语句：
SELECT SUM(Price) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
You can assume all of the tables have been set up properly.
您可以假设所有表都已正确设置。
If I do a select between two dates and there are no items within that date range, the function returns NULL as the TotalPrice rather than 0.
如果我在两个日期之间进行选择并且该日期范围内没有项目，则该函数返回 NULL 作为 TotalPrice 而不是 0。
How can I make sure that if no records are found, 0 gets returned rather than NULL?
如何确保如果没有找到记录，则返回 0 而不是 NULL？
回答by Jonathan Rupp
Most database servers have a COALESCEfunction, which will return the first argument that is non-null, so the following should do what you want:
大多数数据库服务器都有一个COALESCE函数，它将返回第一个非空的参数，所以下面应该做你想做的：
SELECT COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
Since there seems to be a lot of discussion about
由于似乎有很多关于
COALESCE/ISNULL will still return NULL if no rows match, try this query you can copy-and-paste into SQL Server directly as-is:
如果没有行匹配，COALESCE/ISNULL 仍将返回 NULL，试试这个查询，你可以直接复制并粘贴到 SQL Server 中：
SELECT coalesce(SUM(column_id),0) AS TotalPrice
FROM sys.columns
WHERE (object_id BETWEEN -1 AND -2)
Note that the where clause excludes all the rows from sys.columns from consideration, but the 'sum' operator still results in a single row being returned that is null, which coalesce fixes to be a single row with a 0.
请注意，where 子句将 sys.columns 中的所有行排除在外，但 'sum' 运算符仍会导致返回单行，该行为 null，将合并固定为带有 0 的单行。
回答by Joseph
回答by BlackTigerX
SELECT 0+COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
回答by Matt
Edit:Looks like everyone else beat me to it haha
编辑：看起来其他人都打败了我哈哈
Found the answer.
找到了答案。
ISNULL()determines what to do when you have a null value.
ISNULL()确定当您有空值时要做什么。
In this case my function returns a null value so I needed specify a 0 to be returned instead.
在这种情况下，我的函数返回一个空值，所以我需要指定一个要返回的 0。
SELECT ISNULL(SUM(Price), 0) AS TotalPrice
FROM Inventory
WHERE (DateAdded
BETWEEN @StartDate AND @EndDate)
回答by Paul
SELECT COALESCE(
(SELECT SUM(Price) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate))
, 0)
If the table has rows in the response it returns the SUM(Price). If the SUM is NULL or there are no rows it will return 0.
如果表在响应中有行，则它返回 SUM(Price)。如果 SUM 为 NULL 或没有行，它将返回 0。
Putting COALESCE(SUM(Price), 0) does NOT work in MSSQL if no rows are found.
如果未找到行，则放置 COALESCE(SUM(Price), 0) 在 MSSQL 中不起作用。
回答by dkretz
You could use
你可以用
SELECT ISNULL(SUM(ISNULL(Price, 0)), 0).
SELECT ISNULL(SUM(ISNULL(Price, 0)), 0).
I'm 99% sure that will work.
我 99% 肯定这会奏效。
回答by tharindu_DG
ORACLE/PLSQL:
甲骨文/PLSQL：
NVL FUNCTION
NVL 功能
SELECT NVL(SUM(Price), 0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
This SQL statement would return 0if the SUM(Price)returned a null value. Otherwise, it would return the SUM(Price)value.
0如果SUM(Price)返回空值，则此 SQL 语句将返回。否则，它将返回SUM(Price)值。
回答by Garry Owen
The easiest way to do this is just to add zero to your result.
最简单的方法就是将零添加到您的结果中。
i.e.
IE
$A=($row['SUM'Price']+0);
echo $A;
hope this helps!!
希望这可以帮助！！
