带有“ANY”的PostgreSQL查询无效

SELECT "Ticket_id"  FROM "Tickets"
 WHERE "Status" = 1 AND ("Ticket_id" !=  ANY(array[1,2,3])) Limit 6

And the result is 1,2,3,4,5,6

结果是1 2 3 4 5 6

2 个解决方案

#1

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

你想要使用所有，而不是任何。从精美的手册:

9.21.3. ANY/SOME (array)

9.21.3。任何/一些(数组)

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.

[…]使用给定的运算符对左边的表达式进行计算，并与数组的每个元素进行比较，这必须产生一个布尔结果。如果得到任何真实的结果，则任何结果都是“真实的”。

So if we say this:

如果我们说

1 != any(array[1,2])

then we'll get true since (1 != 1) or (1 != 2) is true. ANY is essentially an OR operator. For example:

因为(1 != 1)或者(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)

If we look at ALL, we see:

如果我们仔细看看，就会发现:

9.21.4. ALL (array)

9.21.4。(数组)

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...

[…]使用给定的运算符对左边的表达式进行计算，并与数组的每个元素进行比较，这必须产生一个布尔结果。所有的结果都是“真实的”，如果所有的比较结果都是真的……

so if we say this:

如果我们说

1 != all(array[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:

然后我们会得到false，因为(1 != 1)和(1 != 2)都是假的，我们看到所有的都是和运算符。例如:

=> 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:

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

select "Ticket_id"
from "Tickets"
where "Status" = 1
  and "Ticket_id" != all(array[1,2,3])
limit 6

#2

Do you mean:

你的意思是:

"Ticked_id" NOT IN (1,2,3)