从 SQL 函数获取单个值时，如何将 NULL 更改为 0? [英] How can I change NULL to 0 when getting a single value from a SQL function?

问题描述

我有一个查询，计算两个日期之间所有商品的价格.这是选择语句:

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.

如果我在两个日期之间进行选择并且该日期范围内没有项目，则该函数返回 NULL 作为 TotalPrice 而不是 0.

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.

如何确保如果没有找到记录，则返回 0 而不是 NULL?

How can I make sure that if no records are found, 0 gets returned rather than NULL?

推荐答案

大多数数据库服务器都有一个 COALESCE 函数，它将返回第一个非空的参数，所以下面应该做你想做的:

Most database servers have a COALESCE function, which will return the first argument that is non-null, so the following should do what you want:

SELECT COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

因为似乎有很多关于

如果没有行匹配，COALESCE/ISNULL 仍然会返回 NULL，试试这个查询，你可以直接复制并粘贴到 SQL Server 中:

COALESCE/ISNULL will still return NULL if no rows match, try this query you can copy-and-paste into SQL Server directly as-is:

SELECT coalesce(SUM(column_id),0) AS TotalPrice 
FROM sys.columns
WHERE (object_id BETWEEN -1 AND -2)

请注意，where 子句将 sys.columns 中的所有行排除在考虑范围之外，但sum"运算符仍会导致返回单行，该行为空，这将合并固定为带有 0 的单行.

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.

这篇关于从 SQL 函数获取单个值时，如何将 NULL 更改为 0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！