SQL大小写表达式语法？

Question

SQL Case表达式的完整正确语法是什么？

Answer 1

在完整的语法取决于您正在使用的数据库引擎：对于SQL Server：CASE case-expression    WHEN when-expression-1 THEN value-1  [ WHEN when-expression-n THEN value-n ... ]  [ ELSE else-value ]END要么：CASE    WHEN boolean-when-expression-1 THEN value-1  [ WHEN boolean-when-expression-n THEN value-n ... ]  [ ELSE else-value ]END表达式等：case-expression    - something that produces a valuewhen-expression-x  - something that is compared against the case-expressionvalue-1            - the result of the CASE statement if:                         the when-expression == case-expression                      OR the boolean-when-expression == TRUEboolean-when-exp.. - something that produces a TRUE/FALSE answer链接：CASE（Transact-SQL）还要注意，WHEN语句的顺序很重要。您可以轻松编写多个重叠的WHEN子句，并使用第一个匹配的子句。注意：如果未指定ELSE子句，并且找不到匹配的WHEN条件，则CASE表达式的值为NULL。

Answer 2

考虑到您标记了多个产品，我想说的是完全正确的语法是在ISO / ANSI SQL-92标准中找到的语法：<case expression> ::=&nbsp; &nbsp; &nbsp; &nbsp;<case abbreviation>&nbsp; &nbsp; &nbsp;| <case specification><case abbreviation> ::=&nbsp; &nbsp; &nbsp; &nbsp;NULLIF <left paren> <value expression> <comma>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <value expression> <right paren>&nbsp; &nbsp; &nbsp;| COALESCE <left paren> <value expression>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; { <comma> <value expression> }... <right paren><case specification> ::=&nbsp; &nbsp; &nbsp; &nbsp;<simple case>&nbsp; &nbsp; &nbsp;| <searched case><simple case> ::=&nbsp; &nbsp; &nbsp;CASE <case operand>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <simple when clause>...&nbsp; &nbsp; &nbsp; &nbsp; [ <else clause> ]&nbsp; &nbsp; &nbsp;END<searched case> ::=&nbsp; &nbsp; &nbsp;CASE&nbsp; &nbsp; &nbsp; &nbsp;<searched when clause>...&nbsp; &nbsp; &nbsp;[ <else clause> ]&nbsp; &nbsp; &nbsp;END<simple when clause> ::= WHEN <when operand> THEN <result><searched when clause> ::= WHEN <search condition> THEN <result><else clause> ::= ELSE <result><case operand> ::= <value expression><when operand> ::= <value expression><result> ::= <result expression> | NULL<result expression> ::= <value expression>语法规则1) NULLIF (V1, V2) is equivalent to the following <case specification>:&nbsp; &nbsp; &nbsp;CASE WHEN V1=V2 THEN NULL ELSE V1 END2) COALESCE (V1, V2) is equivalent to the following <case specification>:&nbsp; &nbsp; &nbsp;CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the&nbsp; &nbsp;following <case specification>:&nbsp; &nbsp; &nbsp;CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )&nbsp; &nbsp; &nbsp;END4) If a <case specification> specifies a <simple case>, then let CO&nbsp; &nbsp;be the <case operand>:&nbsp; &nbsp;a) The data type of each <when operand> WO shall be comparable&nbsp; &nbsp; &nbsp; with the data type of the <case operand>.&nbsp; &nbsp;b) The <case specification> is equivalent to a <searched case>&nbsp; &nbsp; &nbsp; in which each <searched when clause> specifies a <search&nbsp; &nbsp; &nbsp; condition> of the form "CO=WO".5) At least one <result> in a <case specification> shall specify a&nbsp; &nbsp;<result expression>.6) If an <else clause> is not specified, then ELSE NULL is im-&nbsp; &nbsp;plicit.7) The data type of a <case specification> is determined by ap-&nbsp; &nbsp;plying Subclause 9.3, "Set operation result data types", to the&nbsp; &nbsp;data types of all <result expression>s in the <case specifica-&nbsp; &nbsp;tion>.Access Rules&nbsp; &nbsp;None.General Rules1) Case:&nbsp; &nbsp;a) If a <result> specifies NULL, then its value is the null&nbsp; &nbsp; &nbsp; value.&nbsp; &nbsp;b) If a <result> specifies a <value expression>, then its value&nbsp; &nbsp; &nbsp; is the value of that <value expression>.2) Case:&nbsp; &nbsp;a) If the <search condition> of some <searched when clause> in&nbsp; &nbsp; &nbsp; a <case specification> is true, then the value of the <case&nbsp; &nbsp; &nbsp; specification> is the value of the <result> of the first&nbsp; &nbsp; &nbsp; (leftmost) <searched when clause> whose <search condition> is&nbsp; &nbsp; &nbsp; true, cast as the data type of the <case specification>.&nbsp; &nbsp;b) If no <search condition> in a <case specification> is true,&nbsp; &nbsp; &nbsp; then the value of the <case expression> is the value of the&nbsp; &nbsp; &nbsp; <result> of the explicit or implicit <else clause>, cast as&nbsp; &nbsp; &nbsp; the data type of the <case specification>.

Answer 3

以下是CASEPostgreSQL文档中的语句示例（此处的Postgres遵循SQL标准）：SELECT a,&nbsp; &nbsp;CASE WHEN a=1 THEN 'one'&nbsp; &nbsp; &nbsp; &nbsp; WHEN a=2 THEN 'two'&nbsp; &nbsp; &nbsp; &nbsp; ELSE 'other'&nbsp; &nbsp;ENDFROM test;要么SELECT a,&nbsp; &nbsp;CASE a WHEN 1 THEN 'one'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN 2 THEN 'two'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 'other'&nbsp; &nbsp;ENDFROM test;显然，当您仅根据一个可能值列表检查一个字段时，第二种形式就更干净了。第一种形式允许更复杂的表达式。