SQL CASE 语句中 WHEN 子句的执行顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24529152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execution order of WHEN clauses in a CASE statement
提问by The Ghost
Given the following body of a case statement:
给定以下 case 语句主体：
1 WHEN r.code= '00' then 'A1'
2 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2' <
3 WHEN r.code ='0120' then 'A3'
4 WHEN r.code ='01' then 'A4' <
5 WHEN r.code ='1560' then 'A5'
6 WHEN r.code ='1530' then 'A6'
7 WHEN r.code ='1550' then 'A7'
I'm assuming line 2 will always execute before line 4? Then I read statements like 'SQL is a declarative language, meaning that it tells the SQL engine what to do, not how' in
我假设第 2 行总是在第 4 行之前执行？然后我读到类似“ SQL 是一种声明性语言，这意味着它告诉 SQL 引擎要做什么，而不是如何”这样的语句
Order Of Execution of the SQL query
and wonder if this also relates to the order of execution in the CASE statement. Essentially, can i leave the code above as it is without having to change line 4 to
并想知道这是否也与 CASE 语句中的执行顺序有关。本质上，我可以保留上面的代码而不必将第 4 行更改为
4 WHEN r.code ='01' AND r.source != 'PXWeb' then 'A4'
回答by Damien_The_Unbeliever
The value that is returned will be the value of the THENexpression for the earliest WHENclause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.
返回的值将是匹配THEN的最早WHEN子句（文本）的表达式的值。这确实意味着如果满足第 2 行条件，结果将为A2.
But, if your THENexpressions were more complex than just literal values, some of the work to evaluatethose expressions may happen even when that expression is not required.
但是，如果您的THEN表达式比文字值更复杂，则即使不需要该表达式，也可能会执行一些评估这些表达式的工作。
E.g.
例如
WHEN r.code= '00' then 'A1'
WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'
WHEN r.code ='0120' then 1/0
WHEN r.code ='01' then 'A4'
couldgenerate a division by zero error even if r.codeisn't equal to 0120, and even if it's equal to 00, say. I don't know what the standard has to say on this particular issue but I know that it is true of some products.
即使r.code不等于0120，即使它等于00，也可能产生除以零错误。我不知道标准对这个特定问题有什么说法，但我知道某些产品确实如此。
回答by The Ghost
Never mind:
没关系：
"The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied."
“CASE 语句按顺序评估其条件，并在满足条件的第一个条件处停止。”
回答by Rahul
AFAIK, The order of CASEevaluation will be the order you have specified in your query. So in your case the order of evaluation will be 1,2,3,4 ... , 7
AFAIK，CASE评估顺序将是您在查询中指定的顺序。所以在你的情况下，评估的顺序是1,2,3,4 ... , 7
can i leave the code above as it is without having to change line 4 to
我可以保留上面的代码，而不必将第 4 行更改为
You can change your 2nd CASEand include an ELSEpart like below which will take care of 4th CASEevaluation and you can remove the 4th evaluation altogether
您可以更改您的第二个CASE并包含ELSE如下所示的部分，该部分将处理第四个CASE评估，您可以完全删除第四个评估
2 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2' ELSE 'A4'
