带有“ANY"的 PostgreSQL 查询不起作用 [英] A PostgreSQL query with 'ANY' is not working

问题描述

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

推荐答案

您想使用 ALL，而不是 ANY.来自精美手册:

You want to use ALL, not ANY. From the fine manual:

9.21.3.任何/一些(数组)

expression operator ANY (array expression)

[...] 使用给定的 operator 评估左侧表达式并将其与数组的每个元素进行比较，这必须产生布尔结果.ANY 的结果是真"，如果得到任何真结果.

[...] 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])

那么我们就会得到 true，因为 (1 != 1) or (1 != 2) 是 true.ANY 本质上是一个 OR 运算符.例如:

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)

如果我们查看 ALL，我们看到:

If we look at ALL, we see:

9.21.4.ALL(数组)

expression operator ALL (array expression)

[...] 使用给定的 operator 评估左侧表达式并将其与数组的每个元素进行比较，这必须产生布尔结果.ALL 的结果是true"，如果所有比较结果都为真...

[...] 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) 是假的，我们看到 ALL 本质上是一个 AND 运算符.例如:

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)

如果要排除数组中的所有值，请使用ALL:

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

这篇关于带有“ANY"的 PostgreSQL 查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！