空结果时，Oracle返回常量 [英] Oracle Return Constant When Empty Result

问题描述

我试图弄清楚当结果集为空时如何返回一个常量.

I am trying to figure out how to return a constant as a result when the result set is empty.

示例

查询为:

select employee_id from employee where name = 'John_Doe';

在这种情况下，系统中不存在John Doe.除了返回空集，我如何返回常量，例如数字0?我已经看到了带有case语句的内容，但是不确定如何实现.

For this case, John Doe does not exist in the system. Instead of returning an empty set, how could I return a constant, such as the number 0 instead? I've seen things with case statements, but I'm not sure how I would implement that.

感谢您的所有帮助.

推荐答案

当我遇到总是总是只返回一行的问题时，我经常使用聚合.如果没有匹配项，则以下内容将返回NULL:

When I'm faced with the problem of always returning exactly one row, I often use aggregation. The following will return NULL if there is no match:

select max(employee_id)
from employee
where name = 'John_Doe';

以下内容返回0:

select coalesce(max(employee_id), 0)
from employee
where name = 'John_Doe';

这篇关于空结果时，Oracle返回常量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！