如何在表中找到匹配的配对？

Question

我有一张桌子，我把日志储存在Postgres里。这些日志包含案例数据和案例编号。每行存储案例编号和更多记录。

示例：

+-----------------+-----------+---------+------+------+
| case number     | action_id | task_id | user | date |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 23456/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 2         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 1         | 6       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 34567/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 6       |      |      |
+-----------------+-----------+---------+------+------+

我想要创建一个查询，其中的结果都是task_id = 7的行，但只包含那些大小写没有action_id=2对的行。

举例结果：

+-----------------+-----------+---------+------+------+
| case number     | action_id | task_id | user | date |
+-----------------+-----------+---------+------+------+
| 23456/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 34567/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+

我必须从日志中做报告，但是我不知道如何执行这个查询。

Answer 1

这个(未经测试)怎么样？

select *
from log_table x
where task_id = 7
and not exists (
  select 1
  from log_table
  where case_number = x.case_number
  and task_id = x.task_id
  and action_id = 2
);