PostgreSQL如何使用子查询比对结果判断记录是否首次符合条件

核心思路 #

使用NOT EXISTS关联子查询嵌入注释位置，在相同外键关联维度下，判断当前记录之前不存在符合筛选规则、state值为OK的历史记录，即可完成「首次满足条件」的校验。

修正后完整代码片段 #

替换你原有注释位置的内容即可，子查询的关联、筛选逻辑和你原有查询规则完全对齐：

where
  1=1
  AND ip.id=62
  AND l.insurance_payment_id=62
  AND (p.created_at::timestamptz at time zone 'America/Santiago' BETWEEN '2022-06-22 00:00:00-04'::timestamptz AND '2022-06-22 23:59:59-04'::timestamptz 
        AND op.id = 9) 
  /* 不存在此前state为OK的匹配记录 */
  AND NOT EXISTS (
    SELECT 1
    FROM payment p_prev -- 替换为你外层查询中p表对应的实际表名
    -- 关联维度和外层保持一致，确保是同一业务关联下的记录
    JOIN insurance_payment ip_prev ON ip_prev.id = p_prev.insurance_payment_id
    JOIN 对应业务表 l_prev ON l_prev.insurance_payment_id = ip_prev.id -- 替换为你外层l表对应的实际关联逻辑
    JOIN op表 op_prev ON op_prev.id = p_prev.op_id -- 替换为你外层op表对应的实际关联逻辑
    WHERE p_prev.metadata @> '{"state":"OK"}'
      -- 时间筛选规则和外层完全对齐，避免时区、区间判断偏差
      AND (p_prev.created_at::timestamptz at time zone 'America/Santiago' BETWEEN '2022-06-22 00:00:00-04'::timestamptz AND '2022-06-22 23:59:59-04'::timestamptz)
      AND ip_prev.id = ip.id
      AND op_prev.id = op.id
      -- 对齐原有排序规则，判断为更早的记录
      AND (p_prev.ps < p.ps OR (p_prev.ps = p.ps AND p_prev.created_at < p.created_at))
  )
  and ps.metadata->>'state' = 'OK') as foo

关键注意点 #

• 子查询中的表名、关联字段必须和你外层查询的实际表结构、JOIN逻辑完全对应，不要直接照搬示例里的关联写法，要匹配你自己的表关系。
• 「更早记录」的判断逻辑必须和你原查询order by ps asc, p.created_at asc的排序规则完全一致，否则会出现首条记录判断和原查询结果不匹配的问题。
• 建议在外键字段、created_at字段、metadata的state查询路径上建立联合索引，避免写入校验时子查询全表扫描影响性能。
• 如果你的业务只需要判断同外键下所有时间的首条OK记录，不需要限定时间区间，可以删掉子查询里的时间区间判断条件。

内容的提问来源于stack exchange，提问作者Diego Villafañe