dws
比较和列表操作符
更新时间：
2019/12/24 GMT+08:00
比较操作符LT、LE、GT、GE、EQ和NE不得用作TABLE别名或COLUMN别名
以下章节介绍了支持的比较和列表操作符。
^=和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';
|
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%' ]);
|
父主题：
系统函数和操作符
相关文档
相关产品
