CASE 句CASE clause

は、ルールを使用して、指定された条件に基づいて特定の結果を返します。これは、他のプログラミング言語の if や else ステートメントに似ています。Uses a rule to return a specific result based on the specified condition, similar to if and else statements in other programming languages.

構文Syntax

CASE [ expression ] { WHEN boolean_expression THEN then_expression } [ ... ]
    [ ELSE else_expression ]
END

パラメーターParameters

  • boolean_expressionboolean_expression

    結果の型に評価される任意の式 booleanAny expression that evaluates to a result type boolean. 論理演算子 (、) を使用して、2つ以上の式を組み合わせることができ AND OR ます。Two or more expressions may be combined together using the logical operators ( AND, OR ).

  • then_expressionthen_expression

    条件に基づく then 式 boolean_expression 。とは then_expressionelse_expression すべて同じ型であるか、共通型に対して強制可能である必要があります。The then expression based on the boolean_expression condition; then_expression and else_expression should all be same type or coercible to a common type.

  • else_expressionelse_expression

    既定の式です。 then_expression とは、 else_expression すべて同じ型であるか、共通型に対して強制可能である必要があります。The default expression; then_expression and else_expression should all be same type or coercible to a common type.

Examples

CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'John', 30),
    (200, 'Mary', NULL),
    (300, 'Mike', 80),
    (400, 'Dan', 50);

SELECT id, CASE WHEN id > 200 THEN 'bigger' ELSE 'small' END FROM person;
+------+--------------------------------------------------+
|  id  | CASE WHEN (id > 200) THEN bigger ELSE small END  |
+------+--------------------------------------------------+
| 100  | small                                            |
| 200  | small                                            |
| 300  | bigger                                           |
| 400  | bigger                                           |
+------+--------------------------------------------------+

SELECT id, CASE id WHEN 100 then 'bigger' WHEN  id > 300 THEN '300' ELSE 'small' END FROM person;
+------+-----------------------------------------------------------------------------------------------+
|  id  | CASE WHEN (id = 100) THEN bigger WHEN (id = CAST((id > 300) AS INT)) THEN 300 ELSE small END  |
+------+-----------------------------------------------------------------------------------------------+
| 100  | bigger                                                                                        |
| 200  | small                                                                                         |
| 300  | small                                                                                         |
| 400  | small                                                                                         |
+------+-----------------------------------------------------------------------------------------------+

SELECT * FROM person
    WHERE
        CASE 1 = 1
            WHEN 100 THEN 'big'
            WHEN 200 THEN 'bigger'
            WHEN 300 THEN 'biggest'
            ELSE 'small'
        END = 'small';
+------+-------+-------+
|  id  | name  |  age  |
+------+-------+-------+
| 100  | John  | 30    |
| 200  | Mary  | NULL  |
| 300  | Mike  | 80    |
| 400  | Dan   | 50    |
+------+-------+-------+