问题描述
SELECT "Ticket_id" FROM "Tickets"
WHERE "Status" = 1 AND ("Ticket_id" != ANY(array[1,2,3])) Limit 6
和结果是1,2,3,4,5,6
And the result is 1,2,3,4,5,6
推荐答案
您想要使用所有
,而不是任何
。从:
You want to use ALL
, not ANY
. From the fine manual:
9.21.3。 ANY / SOME(阵列)
expression operator ANY (array expression)
[...]左侧前pression进行评估,并使用相比,数组的每个元素给定的的运营商的,它必须产生一个布尔结果。结果任何
如果获得任何真正的结果就是真。
[...] 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.
因此,如果我们要说的是:
So if we say this:
1 != any(array[1,2])
然后我们会得到正确的,因为(1!= 1)或(1!= 2)
是真实的。 任何
本质上是一个或
运营商。例如:
then we'll get true since (1 != 1) or (1 != 2)
is true. ANY
is essentially an OR
operator. For example:
=> select id from (values (1),(2),(3)) as t(id) where id != any(array[1,2]);
id
----
1
2
3
(3 rows)
如果我们看一下<$c$c>ALL$c$c>,我们看到:
If we look at ALL
, we see:
9.21.4。 ALL(阵列)
expression operator ALL (array expression)
[...]左侧前pression进行评估,并使用相比,数组的每个元素给定的的运营商的,它必须产生一个布尔结果。 所有结果
是真,如果所有的比较产生真正的...
[...] 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...
因此,如果我们要说的是:
so if we say this:
1 != all(array[1,2])
然后我们会得到错误的,因为(1!= 1)和(1!= 2)
是假的,我们将看到所有
本质上是一个和
运营商。例如:
then we'll get false since (1 != 1) and (1 != 2)
is false and we see that ALL
is essentially an AND
operator. For example:
=> select id from (values (1),(2),(3)) as t(id) where id != all(array[1,2]);
id
----
3
(1 row)
如果要排除数组中的所有值,可以使用所有
:
If you want to exclude all values in an array, use ALL
:
select "Ticket_id"
from "Tickets"
where "Status" = 1
and "Ticket_id" != all(array[1,2,3])
limit 6
这篇关于PostgreSQL的查询与“任意”不工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!