数组 – 具有“ANY”的PostgreSQL查询不起作用

前端之家收集整理的这篇文章主要介绍了数组 – 具有“ANY”的PostgreSQL查询不起作用前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
SELECT "Ticket_id"  FROM "Tickets"
 WHERE "Status" = 1 AND ("Ticket_id" !=  ANY(array[1,2,3])) Limit 6

结果是1,3,4,5,6

你想使用ALL,而不是任何。从 fine manual

9.21.3. ANY/SOME (array)

06000

[…] 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)或(1!= 2)是真的。任何本质上是一个OR运算符。例如:

=> select id from (values (1),(2),(3)) as t(id) where id != any(array[1,2]);
 id 
----
  1
  2
  3
(3 rows)

如果我们看看ALL,we see

9.21.4. ALL (array)

06003

[…] 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)和(1!= 2)是假的,我们看到ALL本质上是一个AND运算符。例如:

=> select id from (values (1),(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,3])
limit 6

猜你在找的Postgre SQL相关文章