Oracle SQL 中的最小值但不是 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21313770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Least value but not NULL in Oracle SQL
提问by Jestem_z_Kozanowa
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
我想在我的过程中使用函数 LEAST 来找到最小值。问题是某些值可能是 NULL，所以如果我这样做
select least(NULL,0,1) from dual
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.
我得到的答案是 NULL，这可能是正确的，这不是我期望返回的。我想获得最少真实的非零值。非常感谢任何帮助。
采纳答案by tenfour
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
Or, if you're doing something more like this, where you can't just use whereto filter the set,
或者，如果你正在做更像这样的事情，你不能只是where用来过滤集合，
select least(expr1,expr2,expr3) from dual
do this:
做这个：
select least(coalesce(expr1, 12345), coalesce(expr2, 12345), coalesce(expr3, 12345)) from dual
Where 12345is a value big enough that it would only be chosen if all other expressions are NULL.
哪里12345是一个足够大的值，只有在所有其他表达式都是NULL.
回答by Jeffrey Kemp
If any argument is NULL, you want to take the least of the other argument(s). If all arguments are NULL, you want to return NULL.
如果任何参数为 NULL，则您希望采用最少的其他参数。如果所有参数均为 NULL，则您希望返回 NULL。
I might use something like this for two arguments:
对于两个参数，我可能会使用这样的方法：
LEAST(NVL(colA,colB), NVL(colB,colA))
It starts getting ugly for >2 arguments though:
但是，对于 >2 个参数，它开始变得丑陋：
LEAST(COALESCE(colA,colB,colC)
,COALESCE(colB,colA,colC)
,COALESCE(colC,colA,colB))
At which point I'd start considering magic values; but this can be buggy (e.g. what if one of the values legitimately isthe magic value?):
那时我将开始考虑魔法值；但这可能有问题（例如，如果其中一个值合法地是魔法值呢？）：
SELECT CASE WHEN r = maxv THEN NULL ELSE r END AS result
FROM (SELECT LEAST(NVL(:colA,maxv)
,NVL(:colB,maxv)
,NVL(:colC,maxv)) AS r, maxv
FROM (SELECT 9.999999999999999999999999999999999999999e125
AS maxv FROM DUAL));
回答by Harry
Using the three expressions in three orders in three 'coalesce' calls and putting these in the 'least' call will result the least of three, ignoring null unless all three expressions are null.
在三个 'coalesce' 调用中以三个顺序使用三个表达式并将它们放在 'least' 调用中将导致三个中的最少一个，除非所有三个表达式都为 null，否则将忽略 null。
select least(coalesce(expr1, expr2, expr3), coalesce(expr2, expr3, expr1), coalesce(expr3, expr1, expr2)) from dual
回答by Lukasz Szozda
One way is to define own version of LEAST/GREATESTfunc:
一种方法是定义自己的LEAST/GREATESTfunc版本：
-- here version for two params
WITH FUNCTION least_improved(p1 IN NUMBER, p2 IN NUMBER) RETURN NUMBER IS
BEGIN
IF p1 IS NULL THEN
RETURN p2;
ELSIF p2 IS NULL THEN
RETURN p1;
ELSE
RETURN LEAST(p1, p2);
END IF;
END;
SELECT least_improved(col1, col2)
FROM table_name;
/
回答by Leonardo Schuler
SELECT LEAST( COALESCE(Col1, INFINITY ), COALESCE(Col2, INFINITY ) , ... , COALESCE(ColN, INFINITY ) ) FROM MY_TABLE
will return the lowest value from the set of columns 1 to N
将返回列 1 到 N 的集合中的最小值
SELECT GREATEST( COALESCE(Col1, -INFINITY ), COALESCE(Col2, -INFINITY ) , ... , COALESCE(ColN, -INFINITY ) ) FROM MY_TABLE
will return the greatest value from column 1 to N
将返回从第 1 列到 N 的最大值
INFINITY: at least in db2, you have the 'infinity' value that can be used as the biggest number of all and "-infinity" that can be used as the lowest number of all. (oracle have different types of infinity I was told)
INFINITY：至少在 db2 中，您拥有可以用作所有中最大数字的 'infinity' 值和可以用作所有中最小数字的“-infinity”。（有人告诉我，oracle 有不同类型的无穷大）
COALESCE: this function will return the first argument that is not null from the list of arguments, eg: COALESCE(NULL,NULL,NULL,NULL,NULL,1 , 2, 3 ,4 ,5) will return 1 (the 1st argument that is not null);
COALESCE：此函数将返回参数列表中第一个不为空的参数，例如： COALESCE(NULL,NULL,NULL,NULL,NULL,1 , 2, 3 ,4 ,5) 将返回 1（第一个参数这不是空的）；
so coalesce is used in this query to replace a null value to the infinity value, making sure this value will not be selected as one of the lowest value, unless all columns have null value, in that case the query will return infinity instead of null
因此在此查询中使用合并将空值替换为无穷大值，确保不会将此值选为最低值之一，除非所有列都具有空值，在这种情况下，查询将返回无穷大而不是空值
to remove the infinity results from the query, just add the where clause checking if all the values are null, you can do that using the coalesce function as well:
要从查询中删除无穷大结果，只需添加 where 子句检查所有值是否为空，您也可以使用 coalesce 函数执行此操作：
WHERE COALESCE( Col1, Col2, ..., ColN ) IS NOT NULL
回答by user3569339
If you have a lot of columns from which you want the min & max, you can UNPIVOT them into a single column, which then makes it simple; exclude NULLS from your single column & use a MIN() or MAX() to get what you want.
如果您有很多列需要最小值和最大值，您可以将它们 UNPIVOT 成一列，这样就变得简单了；从您的单列中排除 NULLS 并使用 MIN() 或 MAX() 来获得您想要的。
I needed to do this with about 50 date columns (not a great db design) and UNPIVOT seemed to be the most sensible / practical way of doing it
我需要用大约 50 个日期列（不是一个很好的数据库设计）来做到这一点，而 UNPIVOT 似乎是最明智/最实用的方法
