OceanBase Cloud快速入门第55期：如何在查询中使用CASE 条件运算符?

本文通过具体示例介绍如何在查询中使用 CASE 子句。

CASE 条件运算符

CASE 条件运算符可以实现类似 IF…ELSE…THEN 的逻辑而不用调用子程序。CASE 条件子句包括简单 CASE 子句和带搜索条件的 CASE 子句。

示例

创建示例表并录入合适的数据。

obclient> CREATE TABLE t_case(id NUMBER NOT NULL PRIMARY KEY, abbr VARCHAR(5));
Query OK, 0 rows affected

obclient> INSERT INTO t_case(id, abbr) VALUES (1,'US'),(2,'UK'),(3,'CN'),(4,'JP');
Query OK, 4 rows affected 
Records: 4  Duplicates: 0  Warnings: 0

obclient> CREATE TABLE t_case2(id NUMBER NOT NULL PRIMARY KEY, c_date DATE );
Query OK, 0 rows affected 

obclient> INSERT INTO t_case2(id,c_date) VALUES (1,'2021-03-01'),(2,'2021-05-08'),(3,'2021-07-07'),(4,'2021-10-11'),(5,'2021-12-12'),(6,'2022-01-05');
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0

在查询中使用简单的 CASE 子句

在查询中使用简单的 CASE 子句并将国家代码缩写翻译为全称。

obclient> SELECT id, abbr, 
    CASE abbr
         WHEN 'US' THEN 'America'
         WHEN 'UK' THEN 'English'
         WHEN 'CN' THEN 'China'
    ELSE 'UNKOWN'
    END full_name
 FROM t_case ;
+----+------+-----------+
| id | abbr | full_name |
+----+------+-----------+
|  1 | US   | America   |
|  2 | UK   | English   |
|  3 | CN   | China     |
|  4 | JP   | UNKOWN    |
+----+------+-----------+
4 rows in set

在查询中使用带搜索条件的 CASE 子句

obclient> SELECT id, c_date,
     CASE
         WHEN DATEDIFF(NOW(), c_date) > 12*30 THEN 'More than one year ago'
         WHEN DATEDIFF(NOW(), c_date) > 9*30 THEN 'More than three quarters ago'
         WHEN DATEDIFF(NOW(), c_date) > 6*30 THEN 'More than half a year ago'
         WHEN DATEDIFF(NOW(), c_date) > 3*30 THEN 'More than a quarter ago'
         WHEN DATEDIFF(NOW(), c_date) >= 0 THEN 'Within a quarter'
         ELSE 'Illegal'
     END "Duration"
 FROM t_case2;
+----+------------+------------------------------+
| id | c_date     | Duration                     |
+----+------------+------------------------------+
|  1 | 2021-03-01 | More than one year ago       |
|  2 | 2021-05-08 | More than three quarters ago |
|  3 | 2021-07-07 | More than half a year ago    |
|  4 | 2021-10-11 | More than a quarter ago      |
|  5 | 2021-12-12 | Within a quarter             |
|  6 | 2022-01-05 | Within a quarter             |
+----+------------+------------------------------+
6 rows in set