在 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
Handling Null in Greatest function in Oracle
提问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... WHEN
structurein 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
/LEAST
of more than two columns. In those scenarios you can use COALESCE
and expand the solution to as many columns you want.
您的问题特别涉及两列,但我遇到过需要GREATEST
/LEAST
超过两列的情况。在这些场景中,您可以使用COALESCE
解决方案并将其扩展到您想要的任意数量的列。
Here is an example with three columns a
, b
, and c
:
这里是三列的例子a
,b
以及c
:
GREATEST(
COALESCE(a, b, c),
COALESCE(b, c, a),
COALESCE(c, a, b)
)
Note that the column ordering of the COALESCE
changes so that each input column is the first element COALESCE
at 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 COALESCE
statements 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 NULL
if either of the parameters is NULL
.
原因COALESCE()
是,如果其中一个参数是,则GREATEST()
返回。NULL
NULL
回答by Alex Poole
Another version using a case expression
to handle the null
values:
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;