Oracle - 选择不为空的计算结果

Question

对于数据集的每一行，存在两个计算选项中只有一个的数据，其他列为 Null。

我的目标是找到最简单的方法来为每一行选择不为空的计算结果。预期成果：

ROW_NUM   result
-------- -------
1          4.5
2          4.56

我的代码：

With DATASET AS ( 
-- column 1 is just for row number, 
-- column 2 and 3 for caculation option1, 
--- columns 4~6 for caculation option2
SELECT 1 ROW_NUM, NULL time1, NULL qty1,  2   time2_1, 2.5  time2_2,  1 qty2 
FROM DUAL
UNION
SELECT 2 ROW_NUM, 4.56 time1, 1   qty1,   NULL time2_1, NULL time2_2, NULL qty2 
FROM DUAL
)
SELECT ROW_NUM, time1/qty1 OPTION1, (time2_1+time2_2)/qty2 OPTION2 
FROM DATASET;

结果：

ROW_NUM   OPTION1 OPTION2
-------- ------- ---------
1                4.5
2      4.56

Answer 1

当 null 时，您可以解码并使用不同的表示形式：

SELECT ROW_NUM, decode(time1/qty1,null,(time2_1+time2_2)/qty2,time1/qty1) result FROM DATASET;

或 nvl

SELECT ROW_NUM, nvl(time1/qty1,(time2_1+time2_2)/qty2,time1/qty1) result FROM DATASET;

NVL 允许您在查询结果中将 null(作为空白返回(替换为字符串。

Answer 2

按如下方式使用COALESCE函数：

With DATASET AS ( 
--each row contain information for either option1 or 2 
SELECT * 
FROM
(
--column 1 is just for row number, column 2 and 3 for caculation option1, columns 4~6 for caculation option2
SELECT 1 ROW_NUM, NULL time1, NULL qty1 ,  2   time2_1 , 2.5  time2_2,  1    qty2 FROM DUAL
UNION
SELECT 2 ROW_NUM, 4.56 time1 , 1   qty1 , NULL time2_1 , NULL time2_2 , NULL qty2 FROM DUAL
) 
)SELECT ROW_NUM, coalesce(time1/qty1,(time2_1+time2_2)/qty2) as result FROM DATASET;

数据库<>小提琴演示

干杯！！