南大通用GBase 8a MPP Cluster关于比较函数和操作符的用法介绍

今天给大家介绍下南大通用GBase 8a中关于比较函数和比较操作符的用法，先给大家介绍下操作符的优先级，由高到低如下，同一行的操作符具有同样的优先级：

BINARY，COLLATE
!
-(unary minus),～(unary bit inversion)
^
*,/,DIV,%,MOD
-,+
<<,>>
&
|
=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
BETWEEN,CASE,WHEN,THEN,ELSE
NOT
&&,AND
OR,XOR
:= 

比较函数和运算符
比较运算的结果是1（TRUE）、0（FALSE）或NULL。
这些运算可用于数字和字符串上。根据需要，字符串将会自动地被转换为数字，而数字也可自动转换为字符串。

说明：

本文中的一些函数（如GREATEST() 和LEAST()）的所得值虽然不包括1 （TRUE）、0（FALSE）或NULL，但对参数值进行比较时，也会基于下述规则。GBase 8a MPP Cluster使用下列规则进行比较：

•如果一个或两个参数是NULL，比较的结果是NULL，除了<=>比较符（含有NULL参数时，<=>的比较结果不是NULL）。

•如果在一个比较操作中两个参数均是字符串，它们作为字符串被比较（默认不区分大小写）。

•如果两个参数均是数值，它们按照数值被比较。

•如果比较操作中，一个参数为字符串，另一个为数值，则将字符串转换为数值后，按照数值进行比较。字符串转换为数值，对于数字开头的字符串，转换为数值的结果是截取前面的数值部分；对于非数字开头的字符串，转换成数值的结果为0。

•十六进制值如果不与一个数字进行比较，那么它将被当作一个二进制字符串。

•如果参数之一是DATETIME列类型，而其他参数是一个常量，在比较执行之前，这个常量被转换为一个时间戳。

•在其它情况下，参数作为浮点（REAL）数字被比较。

注意：
为了安全起见，建议用户在比较时使用完整的DATETIME/DATE/TIME字符串。
为了进行比较，用户可以使用CAST()函数将某个值转为另外一个类型。

1.1 = 等于
语法：a=b

表达式说明：如果两个操作数相等，则返回1。

示例
示例1：两个操作数都是数字。

gbase> SELECT 1 = 0 FROM dual;
+-------+
| 1 = 0 |
+-------+
|     0 |
+-------+
1 row in set

示例2：数字与字符串进行比较。

gbase> SELECT '0' = 0 FROM dual;
+---------+
| '0' = 0 |
+---------+
|       1 |
+---------+
1 row in set

示例3：浮点数与数字进行比较。

gbase> SELECT 0.0 = 0 FROM dual;
+---------+
| 0.0 = 0 |
+---------+
|       1 |
+---------+
1 row in set

示例4：两个操作数都是NULL。

gbase> SELECT NULL = NULL FROM dual;
+--------------+
| NULL = NULL  |
+--------------+
|         NULL |
+--------------+
1 row in set

1.2 <=> NULL值安全等于
语法：a<=>b

表达式说明：这个操作符像“=”操作符一样执行相等比较，但在下面2种情况下，获得的结果与“=”不同：
1.如果所有的操作数都是NULL，那么返回的是1而不是NULL。
2.如果有且只有一个操作数是NULL，那么返回的是0而不是NULL。

示例
示例1：所有操作数为NULL，或部分操作数为NULL。

gbase> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL FROM dual;
+---------+---------------+-------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL  |
+---------+---------------+-------------+
|       1 |             1 |           0 |
+---------+---------------+-------------+
1 row in set

示例2：=操作符的结果

gbase> SELECT 1 = 1, NULL = NULL, 1 = NULL FROM dual;
+-------+-------------+-----------+
| 1 = 1 | NULL = NULL | 1 = NULL  |
+-------+-------------+-----------+
|     1 |        NULL |      NULL |
+-------+-------------+-----------+
1 row in set

1.3 <> , != 不等于
语法：a<>b或a!=b

表达式说明：如果两个操作数不相等，则返回1。如果其中一个操作数是NULL，则返回NULL。

示例

示例1：操作数都为字符串。

gbase> SELECT '01'<>'1' FROM dual;
+-----------+
| '01'<>'1' |
+-----------+
|         1 |
+-----------+
1 row in set

示例2：其中一个操作数为字符串。

gbase> SELECT 01<>'1' FROM dual;
+---------+
| 01<>'1' |
+---------+
|       0 |
+---------+
1 row in set

示例3：操作数都为字符串。

gbase> SELECT 'zapp' <> 'zappp' FROM dual;
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
|                 1 |
+-------------------+
1 row in set

1.4 <= 小于或者等于
语法：a<=b

表达式说明：如果a小于或等于b，则返回1。如果其中一个操作数是NULL，则返回NULL。

示例
示例1：两个操作数都为数字。

gbase> SELECT 0.1 <= 2 FROM dual;
+----------+
| 0.1 <= 2 |
+----------+
|        1 |
+----------+
1 row in set

1.5 < 小于
语法：a<b

表达式说明：如果a小于b，则返回1。如果其中一个操作数是NULL，则返回NULL。

示例
示例1：两个操作数都为数字。

gbase> SELECT 2 < 2 FROM dual;
+-------+
| 2 < 2 |
+-------+
|     0 |
+-------+
1 row in set

1.6 >= 大于或者等于
语法：a>=b

表达式说明：如果a大于或等于b，则返回1。如果其中一个操作数是NULL，则返回NULL。

示例
示例1：两个操作数都为数字。

gbase> SELECT 2 >= 2 FROM dual;
+--------+
| 2 >= 2 |
+--------+
|      1 |
+--------+
1 row in set

1.7 > 大于
语法：a>b

表达式说明：如果a大于b，则返回1。如果其中一个操作数是NULL，则返回NULL。

示例
示例1：两个操作数都为数字。

gbase> SELECT 2 > 2 FROM dual;
+-------+
| 2 > 2 |
+-------+
|     0 |
+-------+
1 row in set

1.8 IS [NOT]
语法：IS [NOT] <NULL|TRUE|FALSE|UNKNOWN>

函数说明：根据一个布尔值来检验一个值，此处的布尔值可以是NULL、TRUE、FALSE或UNKNOWN。

示例
示例1：使用IS语句检验1、0和NULL。

gbase> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN FROM dual;
+-----------+------------+------------------+
| 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN  |
+-----------+------------+------------------+
|         1 |          1 |                1 |
+-----------+------------+------------------+
1 row in set

示例2：使用IS NOT语句检验1、0和NULL。

gbase> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN FROM dual;
+------------------+------------------+----------------------+
| 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN  |
+------------------+------------------+----------------------+
|                1 |                1 |                    0 |
+------------------+------------------+----------------------+
1 row in set

示例3：使用IS语句检验一个值是否是NULL。

gbase> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL FROM dual;
+-----------+-----------+---------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL  |
+-----------+-----------+---------------+
|         0 |         0 |             1 |
+-----------+-----------+---------------+
1 row in set
示例4：使用IS NOT语句检验一个值是否是NULL。

gbase> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL FROM dual;
+---------------+---------------+-------------------+
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL  |
+---------------+---------------+-------------------+
|             1 |             1 |                 0 |
+---------------+---------------+-------------------+
1 row in set

1.9 expr BETWEEN min AND max
语法：expr BETWEEN min AND max

函数说明：如果expr的值在min和max之间（包括min和max），返回1，否则返回0。
若所有参数都是同一类型，则上述关系相当于表达式(min <= expr AND expr <= max)。其它类型的转换根据本章开篇所述规则进行，且适用于三种参数中任意一种。

示例
示例1：所有参数为同一类型，expr不在min和max中。

gbase> SELECT 1 BETWEEN 2 AND 3 FROM dual;
+-------------------+
| 1 BETWEEN 2 AND 3 |
+-------------------+
|                 0 |
+-------------------+
1 row in set

示例2：所有参数为同一类型，expr在min和max中。

gbase> SELECT 'b' BETWEEN 'a' AND 'c' FROM dual;
+-------------------------+
| 'b' BETWEEN 'a' AND 'c' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set

示例3：参数中包含数字和字符串。

gbase> SELECT 2 BETWEEN 2 AND '3' FROM dual;
+---------------------+
| 2 BETWEEN 2 AND '3' |
+---------------------+
|                   1 |
+---------------------+
1 row in set

1.10 expr NOT BETWEEN min AND max
语法：expr NOT BETWEEN min AND max

等同于
NOT(expr BETWEEN min AND max)

1.11 COALESCE(value,...)
语法：COALESCE(value,...)

函数说明：返回值为列表当中的第一个非NULL值，在全部为NULL值的情况下返回值为NULL。

示例
示例1：其中一个参数值为NULL。

gbase> SELECT COALESCE(NULL,1) FROM dual;
+------------------+
| COALESCE(NULL,1) |
+------------------+
|                1 |
+------------------+
1 row in set

示例2：每个参数值都为NULL。

gbase> SELECT COALESCE(NULL,NULL,NULL) FROM dual;
+--------------------------+
| COALESCE(NULL,NULL,NULL) |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set

1.12GREATEST(value1,value2,...)
语法：GREATEST(value1,value2,...)

函数说明：

当有两个或多个参数时，返回值为最大的参数值。
当参数中有一个为NULL时，直接返回NULL。
当参数都是字符串时，默认是不区分大小写的，如果希望进行字符串取值大小写敏感的比较，则在需要敏感的字符串参数前加上BINARY。

这些参数比较使用下列规则：
•如果返回值在INTEGER上下文中或者所有的参数是整型值，那么它们使用整数比较；
•如果返回值在REAL上下文中或者所有的参数是实数值，那么它们使用实数比较；
•如果所有的参数是大小写敏感的字符串，那么参数比较也是大小写敏感的；其它情况下，参数比较大小写不敏感。

示例
示例1：参数值为整型数字。

gbase> SELECT GREATEST(2,0) FROM dual;
+---------------+
| GREATEST(2,0) |
+---------------+
|             2 |
+---------------+
1 row in set

示例2：参数值为浮点型数字。

gbase> SELECT GREATEST(34.0,3.0,5.0,767.0) FROM dual;
+------------------------------+
| GREATEST(34.0,3.0,5.0,767.0) |
+------------------------------+
|                        767.0 |
+------------------------------+
1 row in set

示例3：参数值为字符串，不区分大小写。

gbase> SELECT GREATEST('B','a','C') FROM dual;
+-----------------------+
| GREATEST('B','a','C') |
+-----------------------+
| C                     |
+-----------------------+
1 row in set

示例4：参数值为字符串，字符串参数前加上BINARY，区分大小写。

gbase> SELECT GREATEST('B',BINARY 'a','C') FROM dual;
+------------------------------+
| GREATEST('B',BINARY 'a','C') |
+------------------------------+
| a                            |
+------------------------------+
1 row in set

示例5：参数值中包含NULL，则执行结果为NULL。

gbase> SELECT GREATEST('B',NULL,'C') FROM dual;
+------------------------+
| GREATEST('B',NULL,'C') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set

1.13 expr IN (value,...)
语法：expr IN (value,...)

函数说明

如果expr是IN列表中的任一值，它将返回1，否则返回0。
如果IN列表中的所有值均是常量，那么所有的值被按照expr的类型进行计算和排序。
如果左边的表达式是NULL，或者在列表中没有发现相匹配的值并且列表中的一个表达式是NULL，IN均返回NULL。
IN()语法也可以用于子查询类型。

示例
示例1：expr不是IN列表中的任一值。

gbase> SELECT 2 IN (0,3,5,'8') FROM dual;
+------------------+
| 2 IN (0,3,5,'8') |
+------------------+
|                0 |
+------------------+
1 row in set

示例2：expr是IN列表中的值。

gbase> SELECT '1' IN (0,3,5,'1') as v_1,'1' IN (0,3,5,NULL) as v_null FROM dual;
+-----+--------+
| v_1 | v_null |
+-----+--------+
|   1 |   NULL |
+-----+--------+
1 row in set

示例3：expr的值为NULL。

gbase> SELECT NULL IN (0,3,5,'wefwf') FROM dual;
+-------------------------+
| NULL IN (0,3,5,'wefwf') |
+-------------------------+
|                    NULL |
+-------------------------+
1 row in set

示例4：子查询中包含IN()函数。
示例中用到的表及数据：
CREATE TABLE sc (sno VARCHAR(4), grade INT);
INSERT INTO sc VALUES ('101',82),('102',59),('103',90),('104',88),('106',82);

查询所有课程都及格的同学的学号。

gbase> SELECT sno FROM sc WHERE grade IN (SELECT grade FROM sc WHERE grade>60) GROUP BY sno;
+------+
| sno  |
+------+
| 103  |
| 101  |
| 104  |
| 106  |
+------+
4 rows in set

1.14 expr NOT IN (value,...)
语法：expr NOT IN (value,...)

等价于：NOT(expr IN (value,...))

1.15 ISNULL(expr)
语法：ISNULL(expr)

函数说明：如果expr为NULL，ISNULL()的返回值为1，否则返回值为0。

示例
示例1：expr的值不为NULL。

gbase> SELECT ISNULL(1+1) FROM dual;
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+
1 row in set

示例2：1/0的结果为NULL，ISNULL()的返回值为1。

gbase> SELECT ISNULL(1/0) FROM dual;
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+
1 row in set

示例3：对NULL值使用“=”进行比较，ISNULL()结果为1。

gbase> SELECT ISNULL(NULL=NULL) FROM dual;
+-------------------+
| ISNULL(NULL=NULL) |
+-------------------+
|                 1 |
+-------------------+
1 row in set

ISNULL()函数同IS NULL比较操作符具有一些相同的特性。IS NULL的使用请参考“1.8 IS [NOT]”中的示例3。

1.16 LEAST(value1,value2,...)
语法：LEAST(value1,value2,...)

函数说明：

有两个或者更多的参数，返回最小的参数值。假如任意一个变量为NULL，则LEAST()的返回值为NULL。
LEAST()对参数进行比较所依据的规则与GREATEST()相同。

示例
示例1：参数值为整型数字，返回最小的参数值。

gbase> SELECT LEAST(2,0) FROM dual;
+------------+
| LEAST(2,0) |
+------------+
|          0 |
+------------+
1 row in set

示例2：参数值为浮点型数字，返回最小的参数值。

gbase> SELECT LEAST(34.0,3.0,5.0,767.0) FROM dual;
+---------------------------+
| LEAST(34.0,3.0,5.0,767.0) |
+---------------------------+
|                       3.0 |
+---------------------------+
1 row in set

示例3：参数值为字符串，不区分大小写。

gbase> SELECT LEAST('B','A','C') FROM dual;
+--------------------+
| LEAST('B','A','C') |
+--------------------+
| A                  |
+--------------------+
1 row in set
gbase> SELECT LEAST('B','a', 'C') FROM dual;
+---------------------+
| LEAST('B','a', 'C') |
+---------------------+
| a                   |
+---------------------+
1 row in set

示例4：参数值为字符串，字符串参数前加上BINARY，区分大小写。

gbase> SELECT LEAST(BINARY 'B',BINARY 'a', 'C') FROM dual;
+-----------------------------------+
| LEAST(BINARY 'B',BINARY 'a', 'C') |
+-----------------------------------+
| B                                 |
+-----------------------------------+
1 row in set

示例5：参数值中包含NULL，则执行结果为NULL。

gbase> SELECT LEAST('C',NULL,'B') FROM dual;
+---------------------+
| LEAST('C',NULL,'B') |
+---------------------+
| NULL                |
+---------------------+
1 row in set