PostgreSQL查询问题：排除关联表含特定状态的主表数据

表结构 #

modifications表 #

|id|  name |status|
|1 | Mod 1 |  3   |
|2 | Mod 2 |  3   |
|3 | Mod 3 |  3   |

modification_statuses表 #

|id|modification_id|status|sent_at|
|1 |       1       |  8   |2022-11-28|
|1 |       1       |  11  |2022-11-29|
|1 |       2       |  8   |2022-11-28|
|1 |       3       |  8   |2022-11-28|

查询需求 #

• modifications.status = 3
• modification_statuses.status = 8
• DATE(modification_statuses.sent_at) = '2022-11-28'
• 排除关联modification_statuses表中存在status=11的记录

期望输出：Mod 2和Mod 3

当前错误SQL #

SELECT modifications.* FROM modifications
JOIN modification_statuses ON modification_statuses.modification_id = modifications.id
WHERE modifications.status = 3
  AND modification_statuses.status = 8
  AND (DATE(modification_statuses.sent_at) = '2022-11-28')

修正后的SQL #

方法一：用NOT EXISTS子查询 #

SELECT m.* 
FROM modifications m
JOIN modification_statuses ms 
  ON ms.modification_id = m.id
WHERE m.status = 3
  AND ms.status = 8
  AND DATE(ms.sent_at) = '2022-11-28'
  AND NOT EXISTS (
    SELECT 1 
    FROM modification_statuses ms2 
    WHERE ms2.modification_id = m.id 
      AND ms2.status = 11
  );

方法二：用LEFT JOIN + IS NULL #

SELECT m.* 
FROM modifications m
JOIN modification_statuses ms 
  ON ms.modification_id = m.id
LEFT JOIN modification_statuses ms2 
  ON ms2.modification_id = m.id 
  AND ms2.status = 11
WHERE m.status = 3
  AND ms.status = 8
  AND DATE(ms.sent_at) = '2022-11-28'
  AND ms2.id IS NULL;

问题说明 #

原SQL漏掉了"排除关联表中存在status=11的记录"这个关键条件，所以会错误返回Mod 1——虽然它有符合要求的status=8且发送日期为2022-11-28的记录，但它同时存在status=11的关联记录，必须被过滤。

两种修正方法都能实现需求：

• NOT EXISTS子查询直接检查当前modification是否存在status=11的关联记录，不存在则保留。
• LEFT JOIN关联status=11的记录，通过判断关联结果为NULL来筛选出没有该状态的记录。

内容的提问来源于stack exchange，提问作者Puneet Pandey