在本教程中，我们将学习MySQL中的CASE语句。

CASE语句

我们使用CASE语句检查给定表达式是否满足某些给定条件。

CASE语法

CASE expression
    WHEN condition1 THEN result_1
    WHEN condition2 THEN result_2
    ...
    ELSE default_result
END

其中"表达式"是可选的，并且与" condition1"，" condition2"等匹配。
如果任何条件满足表达式，则返回该结果。
否则，如果存在ELSE结果，则将其返回。

表

在本教程中，我们将使用orders表。

表：订单

mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified        | created             |
+---------+------------+--------+-------------+---------------------+---------------------+
|       1 | e03        |  15.00 | OPEN        | 2016-01-02 03:04:05 | 2016-01-02 03:04:05 |
|       2 | e01        |  25.50 | OPEN        | 2016-01-04 03:04:03 | 2016-01-04 03:04:03 |
|       3 | e05        | 100.70 | CLOSED      | 2016-02-02 03:03:04 | 2016-02-02 03:03:04 |
|       4 | e02        |  22.18 | OPEN        | 2016-01-02 03:04:05 | 2016-01-02 03:04:05 |
|       5 | e04        |   9.50 | CANCELLED   | 2016-01-04 03:04:03 | 2016-01-04 03:04:03 |
|       6 | e04        |  99.99 | OPEN        | 2016-02-02 03:03:04 | 2016-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)

例子1

在下面的示例中，我们将输出"大于15"或者"小于15"或者"等于15"。

注意！在给定的示例中，我们不使用CASE的"表达式"。

mysql> SELECT 
    o.orderid,
    o.amount,
    CASE
        WHEN o.amount > 15 THEN 'Greater than 15'
        WHEN o.amount < 15 THEN 'Less than 15'
        ELSE 'Equal to 15'
    END AS result
FROM
    orders o;

+---------+--------+-----------------+
| orderid | amount | result          |
+---------+--------+-----------------+
|       1 |  15.00 | Equal to 15     |
|       2 |  25.50 | Greater than 15 |
|       3 | 100.70 | Greater than 15 |
|       4 |  22.18 | Greater than 15 |
|       5 |   9.50 | Less than 15    |
|       6 |  99.99 | Greater than 15 |
+---------+--------+-----------------+
6 rows in set (0.00 sec)

例子2

在下面的示例中，我们将显示订单状态。

注意！在下面的示例中，我们使用CASE 表达式。

mysql> SELECT 
    o.orderid,
    o.orderstatus,
    CASE o.orderstatus
        WHEN 'OPEN' THEN 'Order is in open state.'
        WHEN 'CLOSED' THEN 'Order is closed.'
        WHEN 'CANCELLED' THEN 'Order is cancelled.'
        ELSE 'Order is in unknown state.'
    END AS order_summary
FROM
    orders o;

+---------+-------------+-------------------------+
| orderid | orderstatus | order_summary           |
+---------+-------------+-------------------------+
|       1 | OPEN        | Order is in open state. |
|       2 | OPEN        | Order is in open state. |
|       3 | CLOSED      | Order is closed.        |
|       4 | OPEN        | Order is in open state. |
|       5 | CANCELLED   | Order is cancelled.     |
|       6 | OPEN        | Order is in open state. |
+---------+-------------+-------------------------+
6 rows in set (0.00 sec)

例子#3

在下面的示例中，如果没有匹配项，我们将获得NULL。

mysql> SELECT 
    o.orderid,
    o.orderstatus,
    CASE o.orderstatus
        WHEN 'OPEN' THEN 'Order is in open state.'
        WHEN 'CLOSED' THEN 'Order is closed.'
    END AS order_summary
FROM
    orders o;

+---------+-------------+-------------------------+
| orderid | orderstatus | order_summary           |
+---------+-------------+-------------------------+
|       1 | OPEN        | Order is in open state. |
|       2 | OPEN        | Order is in open state. |
|       3 | CLOSED      | Order is closed.        |
|       4 | OPEN        | Order is in open state. |
|       5 | CANCELLED   | NULL                    |
|       6 | OPEN        | Order is in open state. |
+---------+-------------+-------------------------+
6 rows in set (0.00 sec)