如果找不到记录，则返回零 [英] 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屋！