更新时间:2024-06-29 GMT+08:00
比较和列表操作符
以下章节介绍了支持的比较和列表操作符。
![](https://support.huaweicloud.com/intl/zh-cn/tg-dws/public_sys-resources/note_3.0-zh-cn.png)
比较操作符LT、LE、GT、GE、EQ和NE不得用作表别名或列别名。
^=和GT
输入:比较操作(^=和GT)
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1, tab2 t2 WHERE t1.c3 ^= t1.c3 AND t2.c4 GT 100; |
输出:
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1, tab2 t2 WHERE t1.c3 <> t1.c3 AND t2.c4 > 100; |
EQ和NE
输入:比较操作(EQ和NE)
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1 INNER JOIN tab2 t2 ON t1.c2 EQ t2.c2 WHERE t1.c6 NE 1000; |
输出:
1 2 3 4 5 |
SELECT t1.c1, t2.c2 FROM tab1 t1 INNER JOIN tab2 t2 ON t1.c2 = t2.c2 WHERE t1.c6 <> 1000; |
LE和GE
输入:比较操作(LE和GE)
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1, tab2 t2 WHERE t1.c3 LE 200 AND t2.c4 GE 100; |
输出:
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1, tab2 t2 WHERE t1.c3 <= 200 AND t2.c4 >= 100; |
NOT=和LT
输入:比较操作(NOT=和LT)
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1, tab2 t2 WHERE t1.c3 NOT= t1.c3 AND t2.c4 LT 100; |
输出:
1 2 3 4 |
SELECT t1.c1, t2.c2 FROM tab1 t1, tab2 t2 WHERE t1.c3 <> t1.c3 AND t2.c4 < 100; |
IN和NOT IN
详情请参见IN/NOT IN转换。
输入:IN和NOT IN
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c1 IN 'XY'; |
输出:
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c1 = 'XY'; |
![](https://support.huaweicloud.com/intl/zh-cn/tg-dws/public_sys-resources/note_3.0-zh-cn.png)
GaussDB A 和 DWS支持IN和NOT IN操作符,特殊场景除外。
IS NOT IN
输入:IS NOT IN
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c1 IS NOT IN (subquery); |
输出:
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c1 NOT IN (subquery); |
LIKE ALL/NOT LIKE ALL
输入:LIKE ALL/NOT LIKE ALL
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c3 NOT LIKE ALL ('%STR1%', '%STR2%', '%STR3%'); |
输出:
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c3 NOT LIKE ALL (ARRAY[ '%STR1%', '%STR2%', '%STR3%' ]); |
LIKE ANY/NOT LIKE ANY
输入:LIKE ANY/NOT LIKE ANY
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c3 LIKE ANY ('STR1%', 'STR2%', 'STR3%'); |
输出:
1 2 3 |
SELECT c1, c2 FROM tab1 WHERE c3 LIKE ANY (ARRAY[ 'STR1%', 'STR2%', 'STR3%' ]); |
父主题: 函数和操作符