SQL 大小写表达式语法？

SQL Case Expression Syntax?

提问by GateKiller

What is the completeand correct syntax for the SQL Case expression?

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

回答by Lasse V. Karlsen

The completesyntax depends on the database engine you're working with:

在完整的语法取决于您正在使用的数据库引擎：

For SQL Server:

对于 SQL Server：

CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

or:

或者：

CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

expressions, etc:

表达式等：

case-expression    - something that produces a value
when-expression-x  - something that is compared against the case-expression
value-1            - the result of the CASE statement if:
                         the when-expression == case-expression
                      OR the boolean-when-expression == TRUE
boolean-when-exp.. - something that produces a TRUE/FALSE answer

Link: CASE (Transact-SQL)

链接：案例（Transact-SQL）

Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.

另请注意，WHEN 语句的顺序很重要。您可以轻松编写多个重叠的 WHEN 子句，并使用第一个匹配的WHEN 子句。

Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL.

注意：如果没有指定 ELSE 子句，并且没有找到匹配的 WHEN 条件，则 CASE 表达式的值将为NULL。

回答by onedaywhen

Considering you tagged multiple products, I'd say the fullcorrect syntax would be the one found in the ISO/ANSI SQL-92 standard:

考虑到您标记了多个产品，我认为完全正确的语法是 ISO/ANSI SQL-92 标准中的语法：

<case expression> ::=
       <case abbreviation>
     | <case specification>

<case abbreviation> ::=
       NULLIF <left paren> <value expression> <comma>
              <value expression> <right paren>
     | COALESCE <left paren> <value expression>
                { <comma> <value expression> }... <right paren>

<case specification> ::=
       <simple case>
     | <searched case>

<simple case> ::=
     CASE <case operand>
          <simple when clause>...
        [ <else clause> ]
     END

<searched case> ::=
     CASE
       <searched when clause>...
     [ <else clause> ]
     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>

Syntax Rules

语法规则

1) NULLIF (V1, V2) is equivalent to the following <case specification>:

     CASE WHEN V1=V2 THEN NULL ELSE V1 END

2) COALESCE (V1, V2) is equivalent to the following <case specification>:

     CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the
   following <case specification>:

     CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )
     END

4) If a <case specification> specifies a <simple case>, then let CO
   be the <case operand>:

   a) The data type of each <when operand> WO shall be comparable
      with the data type of the <case operand>.

   b) The <case specification> is equivalent to a <searched case>
      in which each <searched when clause> specifies a <search
      condition> of the form "CO=WO".

5) At least one <result> in a <case specification> shall specify a
   <result expression>.

6) If an <else clause> is not specified, then ELSE NULL is im-
   plicit.

7) The data type of a <case specification> is determined by ap-
   plying Subclause 9.3, "Set operation result data types", to the
   data types of all <result expression>s in the <case specifica-
   tion>.

Access Rules

   None.

General Rules

1) Case:

   a) If a <result> specifies NULL, then its value is the null
      value.

   b) If a <result> specifies a <value expression>, then its value
      is the value of that <value expression>.

2) Case:

   a) 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>.

   b) If no <search condition> in a <case specification> is true,
      then the value of the <case expression> is the value of the
      <result> of the explicit or implicit <else clause>, cast as
      the data type of the <case specification>.

回答by Neall

Here are the CASEstatement examples from the PostgreSQL docs(Postgres follows the SQL standard here):

以下是CASEPostgreSQL 文档中的语句示例（Postgres 在此处遵循 SQL 标准）：

SELECT a,
   CASE WHEN a=1 THEN 'one'
        WHEN a=2 THEN 'two'
        ELSE 'other'
   END
FROM test;

or

或者

SELECT a,
   CASE a WHEN 1 THEN 'one'
          WHEN 2 THEN 'two'
          ELSE 'other'
   END
FROM test;

Obviously the second form is cleaner when you are just checking one field against a list of possible values. The first form allows more complicated expressions.

显然，当您只是根据可能值列表检查一个字段时，第二种形式更清晰。第一种形式允许更复杂的表达式。

回答by Eric Johnson

Sybase has the same case syntaxas SQL Server:

Sybase 的大小写语法与 SQL Server相同：

Description

描述

Supports conditional SQL expressions; can be used anywhere a value expression can be used.

支持条件SQL表达式；可以在任何可以使用值表达式的地方使用。

Syntax

句法

case 
     when search_condition then expression 
    [when search_condition then expression]...
    [else expression]
end

Case and values syntax

大小写和值语法

case expression
     when expression then expression 
    [when expression then expression]...
    [else expression]
end

Parameters

参数

case

案件

begins the case expression.

开始 case 表达式。

when

什么时候

precedes the search condition or the expression to be compared.

在搜索条件或要比较的表达式之前。

search_condition

搜索条件

is used to set conditions for the results that are selected. Search conditions for case expressions are similar to the search conditions in a where clause. Search conditions are detailed in the Transact-SQL User's Guide.

用于为选择的结果设置条件。case 表达式的搜索条件类似于 where 子句中的搜索条件。Transact-SQL 用户指南中详细介绍了搜索条件。

then

然后

precedes the expression that specifies a result value of case.

在指定 case 结果值的表达式之前。

expression

表达

is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions” in.

是列名、常量、函数、子查询或列名、常量和由算术或按位运算符连接的函数的任意组合。有关表达式的更多信息，请参阅中的“表达式”。

Example

例子

select disaster, 
       case
            when disaster = "earthquake" 
                then "stand in doorway"
            when disaster = "nuclear apocalypse" 
                then "hide in basement"
            when monster = "zombie apocalypse" 
                then "hide with Chuck Norris"
            else
                then "ask mom"
       end 
  from endoftheworld

回答by Lasse V. Karlsen

I dug up the Oracle page for the same and it looks like this is the same syntax, just described slightly different.

我挖掘了相同的 Oracle 页面，看起来这是相同的语法，只是描述略有不同。

Link: Oracle/PLSQL: Case Statement

链接：Oracle/PLSQL：案例陈述

回答by Leigh Riffel

Oracle syntax from the 11g Documentation:

来自 11g 文档的Oracle语法：

CASE { simple_case_expression | searched_case_expression }
     [ else_clause ]
     END

simple_case_expression

simple_case_expression

expr { WHEN comparison_expr THEN return_expr }...

searched_case_expression

searched_case_expression

{ WHEN condition THEN return_expr }...

else_clause

else_子句

ELSE else_expr

回答by user2001117

Case statement syntax in SQL SERVER:

SQL SERVER 中的 case 语句语法：

CASE column
   WHEN value1 THEN 1
   WHEN value3 THEN 2
   WHEN value3 THEN 3
   WHEN value1 THEN 4
   ELSE ''
END

And we can use like below also:

我们也可以像下面这样使用：

CASE 
   WHEN column=value1 THEN 1
   WHEN column=value3 THEN 2
   WHEN column=value3 THEN 3
   WHEN column=value1 THEN 4
   ELSE ''
END