如何在Oracle中找到负最大值和最小值？

I have a column of type number. It has both positive and negative values. I need to get 4 values : Positive Maximum,Positive Minimum, Negative Maximum,Negative Minimum:

我有一个类型编号的列。它既有正面价值，也有负面价值。我需要获得4个值：正最大值，正最小值，负最大值，负最小值：

a)To get Positive Maximum : I can use Check out the Fiddle

a）获得正面最大值：我可以使用检查小提琴

select max(cola) from test;

b)To get Negative Minimum: I can use Check out the Fiddle

b）获得负最小值：我可以使用Check out the Fiddle

select min(cola) from test;

I have two question here:

我有两个问题：

1)Now i'm not sure how to get the other two values. Guide me to get that

1）现在我不确定如何获得其他两个值。指导我做到这一点

2)Meanwhile while trying this i got another doubt. When i have some column of type varchar2 and it has numbers as value. I'm performing the above operation in this column. Positive maximum is same as above. But Negative minimum is quiet weird. Check Fiddle Here .Why there is no proper implicit conversion is taking place here. Somebody pls explain the reason behind this?

2）同时在尝试这个时我又有了一个疑问。当我有一些varchar2类型的列，它有数字作为值。我正在本专栏中执行上述操作。正极限与上述相同。但负最小值是安静的怪异。在这里检查小提琴。为什么没有适当的隐式转换在这里发生。有人请解释这背后的原因？

4 个解决方案

#1

For question 1, you can easily use case to determine which values you do the min/max on. E.g.:

对于问题1，您可以轻松使用案例来确定您执行最小值/最大值的值。例如。：

select max(case when cola >= 0 then cola end) max_positive,
       min(case when cola >= 0 then cola end) min_positive,
       max(case when cola < 0 then cola end) max_negative,
       min(case when cola < 0 then cola end) min_negative
from   test;

For question 2, when you do the min/max on something that's a varchar, you're going to be doing string comparisons, NOT number comparisons. You have to explicitly convert the values to numbers, since Oracle doesn't know that you expected an implicit conversion to take place. And you shouldn't really rely on implicit conversions anyway. E.g.:

对于问题2，当您对varchar的某些内容执行min / max时，您将进行字符串比较，而不是数字比较。您必须将值显式转换为数字，因为Oracle不知道您希望进行隐式转换。而且你不应该真的依赖隐式转换。例如。：

select max(case when to_number(cola) >= 0 then to_number(cola) end) max_positive,
       min(case when to_number(cola) >= 0 then to_number(cola) end) min_positive,
       max(case when to_number(cola) < 0 then to_number(cola) end) max_negative,
       min(case when to_number(cola) < 0 then to_number(cola) end) min_negative
from   test1;

Here's the SQLFiddle for both cases.

这两个案例都是SQLFiddle。

N.B. I've explicitly split out both negative and positive values (I stuck 0 in with the positive numbers; you'll have to decide how you want to treat rows with a value of 0!), just in case there were no negative numbers or no positive numbers.

注：我已明确地将正值和正值分开（我用正数加上0;你必须决定如何处理值为0的行！），以防万一没有负数或没有正数。

#2

use case expressions within the aggregation functions.

聚合函数中的用例表达式。

e.g.

例如

max(when cola < 0 then cola end) max_neg

max（当可乐<0然后可乐结束时）max_neg

min(cola) min_neg -- no need for case expression here

min（cola）min_neg - 这里不需要大小写表达式

SQL Fiddle

SQL小提琴

Oracle 11g R2 Schema Setup:

Oracle 11g R2架构设置：

Create table test(COLA number);

Insert into test values(1);
Insert into test values(50);
Insert into test values(-65);
Insert into test values(25);
Insert into test values(-2);
Insert into test values(-8);
Insert into test values(5);
Insert into test values(-11);

Create table test1(COLA varchar2(10));

Insert into test1 values('1');
Insert into test1 values('50');
Insert into test1 values('-65');
Insert into test1 values('25');
Insert into test1 values('-2');
Insert into test1 values('-8');
Insert into test1 values('5');
Insert into test1 values('-11');

Query 1:

查询1：

select
      max(case when cola < 0 then cola end) max_neg_cola
    , min(cola) 
    , min(case when cola > 0 then cola end) min_pos_cola
    , max(cola) 
from test

Results:

结果：

| MAX_NEG_COLA | MIN(COLA) | MIN_POS_COLA | MAX(COLA) |
|--------------|-----------|--------------|-----------|
|           -2 |       -65 |            1 |        50 |

#3

1)Now i'm not sure how to get the other two values.

1）现在我不确定如何获得其他两个值。

Use CASE expression.

使用CASE表达式。

SQL> SELECT MAX(
  2    CASE
  3      WHEN cola >= 0
  4      THEN cola
  5    END) max_positive,
  6    MIN(
  7    CASE
  8      WHEN cola >= 0
  9      THEN cola
 10    END) min_positive,
 11    MAX(
 12    CASE
 13      WHEN cola < 0
 14      THEN cola
 15    END) max_negative,
 16    MIN(
 17    CASE
 18      WHEN cola < 0
 19      THEN cola
 20    END) min_negative
 21  FROM test;

MAX_POSITIVE MIN_POSITIVE MAX_NEGATIVE MIN_NEGATIVE
------------ ------------ ------------ ------------
          50            1           -2          -65

SQL>

2)Meanwhile while trying this i got another doubt. When i have some column of type varchar2 and it has numbers as value. I'm performing the above operation in this column. Positive maximum is same as above. But Negative minimum is quiet weird.

2）同时在尝试这个时我又有了一个疑问。当我有一些varchar2类型的列，它有数字作为值。我正在本专栏中执行上述操作。正极限与上述相同。但负最小值是安静的怪异。

You need to first convert the STRING into NUMBER and then use the same query. For less typing of to_number every time, you could use WITH clause.

您需要先将STRING转换为NUMBER，然后使用相同的查询。为了减少每次输入to_number的次数，可以使用WITH子句。

NOTE You must only have numbers in this column, and no alphanumeric. Else, be sure to get ORA-01722: invalid number error.

注意您必须在此列中只有数字，并且不能包含字母数字。否则，请务必获取ORA-01722：无效的数字错误。

SQL> WITH t AS
  2    ( SELECT to_number(cola) cola FROM test1
  3    )
  4  SELECT MAX(
  5    CASE
  6      WHEN cola >= 0
  7      THEN cola
  8    END) max_positive,
  9    MIN(
 10    CASE
 11      WHEN cola >= 0
 12      THEN cola
 13    END) min_positive,
 14    MAX(
 15    CASE
 16      WHEN cola < 0
 17      THEN cola
 18    END) max_negative,
 19    MIN(
 20    CASE
 21      WHEN cola < 0
 22      THEN cola
 23    END) min_negative
 24  FROM t;

MAX_POSITIVE MIN_POSITIVE MAX_NEGATIVE MIN_NEGATIVE
------------ ------------ ------------ ------------
          50            1           -2          -65

SQL>

#4

-1

To get positive minimum value try this..

要获得正的最小值，试试这个..

select min(cola) from test where cola>0;

To get negative maximum value try this..

要获得负的最大值，请试试这个..

select max(cola) from test where cola<0;

#1