在Oracle SQL中值为最低，但不为NULL [英] Least value but not NULL in Oracle SQL

问题描述

我想在我的程序中使用函数LEAST来找到最小值.问题是某些值可能为NULL，所以如果我这样做

I wanted to use function LEAST in my procedure to find the smallest value. The problem is that some of the values might have been NULLs so if I do

select least(NULL,0,1) from dual

我得到的答案是NULL，这可能是正确的，不是我希望返回的东西.我想得到最小的非零值.任何帮助都将不胜感激.

The answer I get is NULL, which is probably correct by is not something I am expecting to return. I would like to get the least real non zero value. Any help greatly appreciated.

推荐答案

我怀疑这实际上是您的查询.也许您正在做更多这样的事情?

I doubt that's actually your query. Maybe you're doing something more like this?

select least(some_column) from dual

如果是这样，请将其更改为此:

If so, change it to this:

select least(some_column) from dual where some_column is not null

---

或者，如果您正在执行类似的操作，则不能仅使用where来过滤集合，

---

Or, if you're doing something more like this, where you can't just use where to filter the set,

select least(expr1,expr2,expr3) from dual

执行以下操作:

select least(coalesce(expr1, 12345), coalesce(expr2, 12345), coalesce(expr3, 12345)) from dual

12345是一个足够大的值，只有在所有其他表达式均为NULL时，才会选择该值.

Where 12345 is a value big enough that it would only be chosen if all other expressions are NULL.

这篇关于在Oracle SQL中值为最低，但不为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！