在完整的語法取決于您正在使用的數(shù)據(jù)庫引擎：

對于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 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

鏈接：CASE（Transact-SQL）

還要注意，WHEN語句的順序很重要。您可以輕松編寫多個重疊的WHEN子句，并使用第一個匹配的子句。

注意：如果未指定ELSE子句，并且找不到匹配的WHEN條件，則CASE表達式的值為NULL。

考慮到您標(biāo)記了多個產(chǎn)品，我想說的是完全正確的語法是在ISO / ANSI SQL-92標(biāo)準(zhǔn)中找到的語法：

<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>

語法規(guī)則

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

以下是CASEPostgreSQL文檔中的語句示例（此處的Postgres遵循SQL標(biāo)準(zhǔn)）：

SELECT a,

   CASE WHEN a=1 THEN 'one'

        WHEN a=2 THEN 'two'

        ELSE 'other'

   END

FROM test;

要么

SELECT a,

   CASE a WHEN 1 THEN 'one'

          WHEN 2 THEN 'two'

          ELSE 'other'

   END

FROM test;

顯然，當(dāng)您僅根據(jù)一個可能值列表檢查一個字段時，第二種形式就更干凈了。第一種形式允許更復(fù)雜的表達式。