我有一个用户ID为status的任务表。我正在尝试编写一个查询来获取status仅为1而不是2的用户ID

SELECT *
FROM users
WHERE EXISTS ( SELECT NULL
               FROM tasks
               WHERE users.id = tasks.user_id
                 AND statis IN (1)       -- must have 1
               )
  AND NOT EXISTS ( SElECT NULL
                   FROM tasks
                   WHERE users.id = tasks.user_id
                     AND statis IN (2)   -- must not have 2
                   )

若要测试多个值，请根据WHERE .. AND statis IN (..)列表中的列表放置值。

通过将子查询与IN()/NOT IN()结合使用，您可以构建任务状态为1/2的用户列表，并根据这些列表筛选您的用户：

SELECT *
FROM users
WHERE
    -- ensure the user has at least 1 task in status 1
    id IN(
        SELECT user_id
        FROM tasks
        WHERE status = 1
    )
    -- ensure the user has no task in status 2
    AND id NOT IN(
        SELECT user_id
        FROM tasks
        WHERE status = 2
    )