PostgreSQL查询需求：获取无特定事件的指定状态路由

以下是符合需求的SQL查询语句，将返回route id为4和5的记录：

SELECT r.id, r.start_day, r.end_day
FROM route r
WHERE 
  -- 该路由下所有关联的route_detail的visit_status均为5
  NOT EXISTS (
    SELECT 1 
    FROM route_detail rd 
    WHERE rd.route_id = r.id AND rd.visit_status != 5
  )
  AND
  -- 该路由下所有关联的route_detail对应的event中不存在event_type=3的记录
  NOT EXISTS (
    SELECT 1 
    FROM route_detail rd 
    JOIN event ev ON ev.route_detail_id = rd.id
    WHERE rd.route_id = r.id AND ev.event_type = 3
  );

逻辑说明 #

1. 第一个NOT EXISTS子查询：确保当前路由下的所有route_detail记录的visit_status都是5，排除那些存在非5状态子记录的路由（如id为2、3的路由）。
2. 第二个NOT EXISTS子查询：检查当前路由下是否存在任何route_detail关联的event中event_type为3的记录，若存在则排除该路由（如id为1的路由）。

原查询的问题 #

原SQL语句存在以下问题导致结果不符合预期：

• 子查询中错误使用了ev.event_type_id !=7，但event表中只有event_type字段，没有event_type_id，该条件无效。
• 子查询通过rd.id = de.id限制为单条route_detail的检查，无法覆盖整个路由下的所有子记录，因此即使路由中存在event_type=3的记录，只要有其他子记录满足条件，该路由仍会被返回。
• 不必要地重复关联route表，增加了查询复杂度且逻辑混乱。

内容的提问来源于stack exchange，提问作者Chris G