如果没有找到记录，则返回零 [英] Return zero if no record is found

问题描述

我在存储过程中有一个查询，它对表中的一些值求和:

I have a query inside a stored procedure that sums some values inside a table:

SELECT SUM(columnA) FROM my_table WHERE columnB = 1 INTO res;

在此选择之后，我用另一个查询检索到的整数减去 res 值并返回结果.如果验证了 WHERE 子句，则一切正常.但如果不是，我的所有函数返回的都是一个空列(可能是因为我试图用一个空值减去一个整数).

After this select I subtract res value with an integer retrieved by another query and return the result. If WHERE clause is verified, all works fine. But if it's not, all my function returns is an empty column (maybe because I try to subtract a integer with an empty value).

如果不满足 WHERE 子句，如何让我的查询返回零?

How can I make my query return zero if the WHERE clause is not satisfied?

推荐答案

您可以:

SELECT COALESCE(SUM(columnA), 0) FROM my_table WHERE columnB = 1
INTO res;

这恰好有效，因为您的查询具有聚合函数，因此总是返回一行，即使在基础表中未找到任何内容.

This happens to work, because your query has an aggregate function and consequently always returns a row, even if nothing is found in the underlying table.

没有聚合的普通查询将返回无行.COALESCE 永远不会被调用，也救不了你.在处理单个列时，我们可以改为包装整个查询:

Plain queries without aggregate would return no row in such a case. COALESCE would never be called and couldn't save you. While dealing with a single column we can wrap the whole query instead:

SELECT COALESCE( (SELECT columnA FROM my_table WHERE ID = 1), 0)
INTO res;

也适用于您的原始查询:

Works for your original query as well:

SELECT COALESCE( (SELECT SUM(columnA) FROM my_table WHERE columnB = 1), 0)
INTO res;

更多关于COALESCE() 在手册中.
详细了解手册中的聚合函数.
更多替代方案在后面的帖子中:

More about COALESCE() in the manual.
More about aggregate functions in the manual.
More alternatives in this later post:

• 如果没有找到值，如何从函数返回一个值

这篇关于如果没有找到记录，则返回零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！