在PostgreSQL中不起作用 [英] NOT IN in postgresql not working

问题描述

我没有得到预期的输出，因为

I am not getting the output as expected, because

 AND ta.task_status_type_id NOT IN ( 10 )

在下面的查询中不起作用。

is not working in below query.

SELECT
    ta.task_id AS id,
    u.employee_id AS employee_id,
    ta.task_status_type_id
FROM
    task_assignments AS ta,
    users AS u
WHERE
    u.id = ta.user_id
    AND ta.id IN (
                   SELECT
                       max ( ta.id ) OVER ( partition BY ta.task_id ) AS id
                   FROM
                       task_details AS td,
                       task_assignments AS ta
                   WHERE
                       td.task_id = ta.task_id
                       AND td.developer_employee_id IS NULL
                       AND ta.task_type_id IN(6,7)
                       AND ta.task_status_type_id NOT IN ( 10 )
                       AND ta.task_status_type_id IN ( 9 )


    );

请帮助解决错误。

推荐答案

有根据的猜测（由于缺少更多信息）：

An educated guess (for lack of more information):

不IN（...） 返回 NULL （如果有任何 NULL 值涉及，并且测试值不在列表中。但是只有 TRUE 符合 WHERE 子句的条件。

NOT IN (...) returns NULL if any NULL values are involved and the tested value is not in the list. But only TRUE qualifies in a WHERE clause.

a NOT IN (b,c)

已转换为：

a <> ALL ('{b,c}'::sometype[])

等效于：

(a <> b AND a <> c )

如果这些值的 任何 （在运算符的任一侧）为 NULL ，您将得到：

If any of these values (on either side of the operator) is NULL, you get:

(NULL AND FALSE)

这是：

NULL

而 NULL 等于 FALSE 中的 WHERE 子句。只有 TRUE 合格。

And NULL is equivalent to FALSE in a WHERE clause. Only TRUE qualifies.

众所周知，这会导致不熟悉三值逻辑。

This has been known to cause disbelieve in users unfamiliar with tri-valued logic.

使用 IS DISTINCT FROM 或 不存在 。或 左联接/是NULL 。

Use IS DISTINCT FROM or NOT EXISTS instead. Or LEFT JOIN / IS NULL.

在这种特殊情况下，您根本不需要缩进的表达式

In this particular case, you don't need the incriminated expression at all

SELECT ta.task_id AS id
      ,u.employee_id
      ,ta.task_status_type_id
FROM   task_assignments ta
JOIN   users            u  ON u.id = ta.user_id
WHERE  ta.id IN (
   SELECT max(ta.id) AS id
   FROM   task_details     td
   JOIN   task_assignments ta USING (task_id)
   WHERE  td.developer_employee_id IS NULL
   AND    ta.task_type_id IN (6,7)
-- AND    ta.task_status_type_id IS DISTINCT FROM 10 -- just cruft
   AND    ta.task_status_type_id = 9                 -- this expression covers it
   GROUP  BY ta.task_id
   )

如果您秘密使用值列表来排除可能与包含列表共享元素的对象：

If you are secretly using a list of values to be excluded that could share elements with the inclusion list:

... 
    AND    (ta.task_status_type_id IN ( ... )) IS NOT TRUE
...

或者您淘汰了NULL值。

否则，请避免在包含和排除列表中使用常见元素。

Or you weed out NULL values.
Or you avoid common elements in inclusion and exclusion list.

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