在另一个验证中查询验证，并引入过滤后的数据PostgreSql

Question

我需要对外部表的所有结果进行分组，只返回没有完成状态的用户

我有以下疑问。

SELECT * from tasks
  LEFT JOIN (SELECT td.task_id, td.user_id FROM tasks 
  LEFT JOIN tasks_data td ON tasks.id = td.task_id AND td.status 
    NOT IN ('Complete')) tasks_data ON tasks_data.task_id = tasks.id
WHERE tasks.id=46337;

数据库中的关系如下：

一个任务有许多用户。用户有很多评论。(tasks_data(

我需要获取所有尚未键入状态"已完成"的用户

EJ：

| Task_id | User_id | Status   |
|---------|---------|----------|
| 46337   | 158     | Pending  |
| 46337   | 1       | Pending  |
| 46337   | 158     | Complete |
| 46337   | 55      | Pending  |
| 46337   | 58      | Pending  |
| 46337   | 1       | Complete |

您应该收到以下结果：

| Task_id | User_id | Status  |
|---------|---------|---------|
| 46337   | 55      | Pending |
| 46337   | 58      | Pending |

Answer 1

以下查询适用于示例表。(在"EJ："文本下(

SELECT * FROM tasks_data
WHERE user_id NOT IN (
  SELECT user_id FROM tasks_data WHERE status = 'Complete'
)
AND task_id = 46337;

Answer 2

我设法过滤了如下结果

select *
from tasks
left join tasks_users tu on tasks.id = tu.task_id
left join tasks_data td ON td.task_id=tasks.id
WHERE
tu.user_id NOT IN (
  SELECT user_id FROM tasks_data WHERE tasks_data.status = 'Complete' and tasks_data.task_id = tasks.id
)