在 Oracle 的 Greatest 函数中处理 Null

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/19186283/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:58:53  来源:igfitidea点击:

Handling Null in Greatest function in Oracle

sqloracle

提问by Raj A

I want to compare two dates from two columns and get the greatest and then compare against a date value.The two column can hold NULL values too.For example I want the below OUTPUT.How do I use the greatest function or if there is anything else.I am again using the output to compare against another date.

我想比较两列中的两个日期并获得最大值,然后与日期值进行比较。这两列也可以保存 NULL 值。例如,我想要下面的 OUTPUT。我如何使用最大的函数或者如果有任何东西else.我再次使用输出与另一个日期进行比较。

Col A         Col  B          OUTPUT
---------------------------------------
 NULL          NULL            NULL
 09/21/2013    01/02/2012      09/21/2013
 NULL          01/03/2013      01/03/2013 
 01/03/2013    NULL            01/03/2013 

采纳答案by Frosty Z

Use Oracle CASE... WHENstructurein your select:

在您的选择中使用 OracleCASE... WHEN结构

SELECT COLA, COLB, CASE
  WHEN (COLA > COLB OR COLB IS NULL)
    THEN COLA
  WHEN (COLA < COLB OR COLA IS NULL)
    THEN COLB
  WHEN (COLA = COLB)
    THEN COLA
  ELSE NULL
  END
  AS OUTPUT
FROM ...

回答by Mr. Llama

Your question specifically involves two columns, but I've run into situations where I needed GREATEST/LEASTof more than two columns. In those scenarios you can use COALESCEand expand the solution to as many columns you want.

您的问题特别涉及两列,但我遇到过需要GREATEST/LEAST超过两列的情况。在这些场景中,您可以使用COALESCE解决方案并将其扩展到您想要的任意数量的列。

Here is an example with three columns a, b, and c:

这里是三列的例子ab以及c

GREATEST(
    COALESCE(a, b, c),
    COALESCE(b, c, a),
    COALESCE(c, a, b)
)

Note that the column ordering of the COALESCEchanges so that each input column is the first element COALESCEat least once. The only time this will return NULL is when all input columns are NULL.

请注意,列顺序的COALESCE更改使每个输入列COALESCE至少成为第一个元素一次。只有当所有输入列都为 NULL 时才会返回 NULL。

In the "general solution" the number of COALESCEstatements will be equal to the number of input columns:

在“通用解决方案”中,COALESCE语句数将等于输入列数:

GREATEST(
    COALESCE(col1, col2, col3, col4, ....),
    COALESCE(col2, col3, col4, ...., col1),
    COALESCE(col3, col4, ...., col1, col2),
    COALESCE(col4, ...., col1, col2, col3),
    COALESCE(...., col1, col2, col3, col4),
    ...
)

回答by David Faber

You might try the following:

您可以尝试以下操作:

SELECT cola, colb, COALESCE( GREATEST( cola, colb ), cola, colb ) AS output
  FROM yourtable;

The reason for COALESCE()is that GREATEST()returns NULLif either of the parameters is NULL.

原因COALESCE()是,如果其中一个参数是,则GREATEST()返回。NULLNULL

回答by Alex Poole

Another version using a case expressionto handle the nullvalues:

a case expression用于处理null值的另一个版本:

select cola, colb, 
  case when cola is null and colb is null then null
    when cola is null then colb
    when colb is null then cola
    else greatest(cola, colb)
  end as output
from <table>;

COLA       COLB       OUTPUT   
---------- ---------- ----------

09/21/2013 01/02/2012 09/21/2013 
           01/03/2013 01/03/2013 
01/03/2013            01/03/2013 

回答by Dennis A.

You could remove the possibility of any of the columns returning NULL by using the NVL function. Substitute any NULL values with a date that is earlier than any date that is likely to occur in your tables.

您可以使用 NVL 函数消除任何列返回 NULL 的可能性。用早于表中可能出现的任何日期的日期替换任何 NULL 值。

SELECT GREATEST(NVL(A,TO_DATE('01/01/1800','MM/DD/YYYY')), 
                NVL(B,TO_DATE('01/01/1800','MM/DD/YYYY'))) AS OUTPUT
FROM ...

The GREATEST function will then return the most recent date (maximum date) from the list of supplied dates without inadvertently returning NULL if one or more of the columns contains NULL.

如果一列或多列包含 NULL,则 GREATEST 函数将从提供的日期列表中返回最近的日期(最大日期),而不会无意中返回 NULL。

回答by tbone

If you have many columns to compare (more than 2 or 3), then handling all the various CASE combinations might get unwieldy. You could try (11g):

如果您有很多列要比较(超过 2 或 3 个),那么处理所有不同的 CASE 组合可能会变得笨拙。你可以试试(11g):

with x as (
  select 1 as id, sysdate - 30 as col1, sysdate-50 as col2, sysdate-20 as col3,null as col4, sysdate-1 as col5 from dual
  union
  select 2 as id, sysdate - 10 as col1, sysdate-20 as col2, null as col3,null as col4, sysdate-35 as col5 from dual
  union
  select 3 as id, null as col1, null as col2, null as col3, null as col4, null as col5 from dual
)
select id, max(dates)
from x
UNPIVOT INCLUDE NULLS
(dates FOR colname IN (col1,col2,col3,col4,col5))
group by id

回答by HRawashdeh

use NVL to solve this however complicity will be increased based of number of compared columns :

使用 NVL 来解决这个问题,但是会根据比较列的数量增加共犯性:

select A.*, *greatest("COL A", "COL B") "DIRECT COMPARE"*, **greatest(nvl("COL A", "COL B"), nvl("COL B", "COL A")) "NVL COMPARE"**
from (
SELECT NULL "COL A", SYSDATE "COL B", SYSDATE "NEEDED RESULT" FROM DUAL UNION
SELECT SYSDATE - 180 , NULL  , SYSDATE - 180 FROM DUAL UNION
SELECT SYSDATE - 180 , SYSDATE , SYSDATE FROM DUAL ) A;

回答by Hedinn

Something like

就像是

SELECT CASE WHEN ColA is NULL and ColB is NULL then NULL
WHEN coalesce(ColA, '01/01/1753')>coalesce(ColB, '01/01/1753') then ColA
ELSE ColB END as Output

回答by Raj A

I tried this..found after googling

我试过这个..谷歌搜索后发现

WITH ABC AS ( SELECT NULL AS col1 , NULL AS col2 FROM dual UNION
 SELECT NULL , DATE'2013-08-12' FROM dual UNION
  SELECT  DATE'2013-08-12' , NULL FROM dual UNION
   SELECT  DATE'2013-08-12',  DATE'2013-09-12' FROM dual)

   SELECT col1, col2 , substr(greatest('x'||col1,'x'||col2),2)
      FROM ABC;