ORACLE，忽略null to sum

Question

我想添加一行的两列(数字类型)，但是当其中一个列为null时，结果为null。我用NVL函数(NVL(Col1，0)+ NVL(col2,0))将其处理。但是，当两者都为空(而不是0)时，我想返回null。我该怎么处理呢？ORACLE中是否有忽略null的函数来对一行的两列进行求和？

| col1 | col2  |                                     |
|:---- |:-----:|:-----------------------------------:|
|  1   |   2   |      --> result have to be : 3      |
|  1   |  null |     --> result have to be : 1       |
| null |  null |     --> result have to be : null    |

Answer 1

coalesce(col1+col2,col1,col2)会更容易：

with t(col1,col2) as (
  select 0,1 from dual union all
  select 2,null from dual union all
  select null,3 from dual union all
  select null,null from dual
  )
select 
  col1,col2,
  coalesce(col1+col2,col1,col2) sum_cols  
from t;

结果：

      COL1       COL2   SUM_COLS
---------- ---------- ----------
         0          1          1
         2       null          2
      null          3          3
      null       null       null

4 rows selected.

如果要和的列更多，则使用sum进行子查询：

(select sum(column_value) from table(sys.odcinumberlist(col1,col2,...,colN)))

示例：

with t(col1,col2) as (
  select 0,1 from dual union all
  select 2,null from dual union all
  select null,3 from dual union all
  select null,null from dual
  )
select 
  col1,col2,
  coalesce(col1+col2,col1,col2) sum_cols,
  (select sum(column_value) from table(sys.odcinumberlist(col1,col2))) sum_cols2
from t;

DBFiddle：18&fiddle=eda8de9746f1d0def3c290420adbb705

Answer 2

在这里，CASE表达式可能是最简单的方法：

SELECT CASE WHEN col1 IS NULL AND col2 IS NULL
            THEN NULL
            ELSE NVL(col1, 0) + NVL(col2, 0) END AS output
FROM yourTable;