SELECT "Ticket_id" FROM "Tickets"
WHERE "Status" = 1 AND ("Ticket_id" != ANY(array[1,2,3])) Limit 6
结果是 1,2,3,4,5,6
SELECT "Ticket_id" FROM "Tickets"
WHERE "Status" = 1 AND ("Ticket_id" != ANY(array[1,2,3])) Limit 6
结果是 1,2,3,4,5,6
ALL
而不是ANY
。来自精细手册:
9.21.3. ANY/SOME (array)
expression operator ANY (array expression)
[...] The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of
ANY
is "true" if any true result is obtained.
如果我们这样说:
1 != any(array[1,2])
如果我们执行 (1 != 1) or (1 != 2)
,那么结果将为真。 ANY
实际上就是一个 OR
运算符。例如:
=> select id from (values (1),(2),(3)) as t(id) where id != any(array[1,2]);
id
----
1
2
3
(3 rows)
如果我们查看ALL
,我们会发现:
9.21.4. ALL (array)
expression operator ALL (array expression)
[...] The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of
ALL
is "true" if all comparisons yield true...
那么如果我们这样说:
1 != all(array[1,2])
如果我们执行(1 != 1) and (1 != 2)
,那么会得到false,因为这个表达式的结果是false,同时我们可以看出ALL
实际上就是一个AND
运算符。例如:
=> select id from (values (1),(2),(3)) as t(id) where id != all(array[1,2]);
id
----
3
(1 row)
如果您想排除数组中的所有值,请使用 ALL
:
select "Ticket_id"
from "Tickets"
where "Status" = 1
and "Ticket_id" != all(array[1,2,3])
limit 6
您的意思是:
"Ticked_id" NOT IN (1,2,3)