從每一列中選擇最大值，即使其中一個值為空

我有一張road_age_test桌子：

create table road_age_test (
   surface_year int,
   base_year int
);

insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (surface_year, base_year) values (30,null);
insert into road_age_test (surface_year, base_year) values (null,40);
insert into road_age_test (surface_year, base_year) values (null,null);
insert into road_age_test (surface_year, base_year) values (50,50);

commit;

select * from road_age_test;

SURFACE_YEAR  BASE_YEAR
------------ ----------
         10         20
         30       null    
       null         40
       null       null
         50         50

我想從每一列中選擇最大值，即使其中一個值為空：

GREATEST_YEAR
-------------
          20
          30
          40
        null
          50

最簡單的方法是什麼？

---

注意：如果兩個值都是null，我想返回null，而不是0。

select
GREATEST(nvl(surface_year,0),nvl(base_year,0)) as greatest_year
from
road_age_test;

--Edit using NULLIF 
select
NULLIF(GREATEST(nvl(surface_year,0),nvl(base_year,0)),0) as greatest_year
from
road_age_test;

--Adding a segmented version for readability (some find it worse, eye of the beholder I guess)
select
NULLIF(
   GREATEST(
           nvl(surface_year,0),
           nvl(base_year,0) --Just keep adding columns here if you need to
           )
     ,0) as greatest_year
from
road_age_test;

無法想像它的效率要高得多（並且由於索引不應該在 where 子句中使用），但它比 case 語句更簡潔，並且允許您輕鬆添加更多值（列）。

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm

引用自：https://dba.stackexchange.com/questions/212781