我在
documentation of case statment中发现它使用了短路:
Oracle Database uses short-circuit evaluation. That is,for a simple
CASE expression,the database evaluates each comparison_expr value
only before comparing it to expr,rather than evaluating all
comparison_expr values before comparing any of them with expr.
Consequently,Oracle never evaluates a comparison_expr if a prevIoUs
comparison_expr is equal to expr. For a searched CASE expression,the
database evaluates each condition to determine whether it is true,and
never evaluates a condition if the prevIoUs condition was true.
但是以下sql返回的除数等于零:
WITH data AS (SELECT 1 AS cond,10 AS num,0 AS div FROM DUAL) SELECT CASE WHEN cond = 2 THEN (CASE WHEN MAX(div) = 0 THEN 0 ELSE SUM(num / div) END) ELSE -1 END AS result FROM data GROUP BY cond
任何避免除数的解决方案都等于零误差?
编辑
此查询工作正常:
WITH data AS (SELECT 1 AS cond,0 AS div FROM DUAL) SELECT CASE WHEN cond = 2 THEN (CASE WHEN MAX(div) = 0 THEN 0 ELSE 1 END) ELSE -1 END AS result FROM data GROUP BY cond
使用
CASE WHEN cond = 2 THEN SUM(case when div = 0 then 0 else num / div end) ELSE -1 END