sql - 搜索 CASE WHEN 处理订单

Question

我需要一些关于Searched 处理顺序的信息 CASE WHEN SQL 语句。

例如:

(CASE
     WHEN Cells.Type <> 'UOM' THEN 0
     WHEN Template_Colors.Uom_Case = 'ONE' THEN 1
     WHEN Template_Colors.Uom_Case = 'MUL' THEN 2
 END) AS CELL_WHAT_UOM

我需要结果总是 0 , 除非 Cells.Type <> 'UOM' .

我知道 SQL 解析器通常从下往上进行，但在 Oracle 中查询似乎工作正常，所以我想 WHEN子句按它们的顺序处理。

100% 保证 0案件先于其他案件处理？

或者我不得不写:

(CASE
     WHEN Cells.Type <> 'UOM' THEN 0
     WHEN Cells.Type = 'UOM' AND Template_Colors.Uom_Case = 'ONE' THEN 1
     WHEN Cells.Type = 'UOM' AND Template_Colors.Uom_Case = 'MUL' THEN 2
 END) AS CELL_WHAT_UOM

...?

我需要有关 Oracle 10g + SQL Server 2008 r2 + Postgre 的信息。

Answer 1

如果Cells.Type不等于 UOM ，您保证结果将为 0。

您不能保证(当然在 SQL Server 中)是不会计算其他分支中的任何表达式。

所以，如果您执行了以下操作:

CASE WHEN a = 0 THEN 0 ELSE 10/a END

您可能会遇到被零除的错误。

---

而且，即使在您的示例中:

WHEN Cells.Type = 'UOM' AND Template_Colors.Uom_Case = 'ONE' THEN ...

不保证谓词的求值顺序，所以Template_Colors.Uom_Case仍然可以访问。不确定您是否担心此列是否被访问，因此目前无法提供进一步的建议。

---

SQL Server documentation :

• Evaluates, in the order specified, Boolean_expression for each WHEN clause.

• Returns result_expression of the first Boolean_expression that evaluates to TRUE.

• If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Oracle :

In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null.

PostGre :

If the result is true then the value of the CASE expression is the result that follows the condition. If the result is false any subsequent WHEN clauses are searched in the same manner.

Ansi 92 (draft) :

If the <search condition> of some <searched when clause> in a <case specification> is true, then the value of the <case
specification> is the value of the <result> of the first (leftmost) <searched when clause> whose <search condition> is true, cast as the data type of the <case specification>.