南大通用GBase 8a的DECODE函数使用例子

GBase 8a数据库集群支持decode函数，用来做批量匹配，简化case when的重复语法。本文介绍函数用法和一些使用例子。

语法

DECODE(value,value1,result1, value2,result2, value3,result3,... , result)

说明

类似于 CASE value WHEN value1 THEN result1 ....，唯一区别是如果 value 为NULL 值，可以和后面的 NULL 值匹配。

样例

数值是成对匹配的，且有默认值。

gbase> select decode(1,0,'Zero',1,'First',2,'Second','Other');
+-------------------------------------------------+
| decode(1,0,'Zero',1,'First',2,'Second','Other') |
+-------------------------------------------------+
| First                                           |
+-------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

没有默认值

不影响匹配结果，但则不匹配时返回NULL

gbase> select decode(1,0,'Zero',1,'First',2,'Second');
+-----------------------------------------+
| decode(1,0,'Zero',1,'First',2,'Second') |
+-----------------------------------------+
| First                                   |
+-----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select decode(11,0,'Zero',1,'First',2,'Second');
+------------------------------------------+
| decode(11,0,'Zero',1,'First',2,'Second') |
+------------------------------------------+
| NULL                                     |
+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

匹配NULL

gbase> select decode(null,0,'Zero',1,'First',2,'Second',null,'NULL_VALUE','Other');
+----------------------------------------------------------------------+
| decode(null,0,'Zero',1,'First',2,'Second',null,'NULL_VALUE','Other') |
+----------------------------------------------------------------------+
| NULL_VALUE                                                           |
+----------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)