基于特定条件检索PostgreSQL表中entity_id的SQL查询需求

表结构 #

CREATE TABLE entity_changes (
    entity_id INTEGER,
    raw_time BIGINT,
    resolved_datetime TIMESTAMP WITH TIME ZONE,
    old_phase VARCHAR(255),
    new_phase VARCHAR(255)
);

该表用于跟踪实体的阶段变更历史，每行记录对应一个实体的一次阶段切换，包含时间信息及变更前后的阶段。

查询需求 #

需检索满足以下条件的entity_id：

• 存在new_phase为'Accept'的变更记录；
• 同一entity_id在该Accept变更之后，不存在从'Accept'到'Review'的后续变更。

示例数据 #

根据示例数据，应返回entity_id=2——因为entity_id=1在Accept变更后存在后续的Accept→Review变更，不符合要求。

解决方案SQL #

SELECT DISTINCT ec.entity_id
FROM entity_changes ec
WHERE ec.new_phase = 'Accept'
AND NOT EXISTS (
    SELECT 1
    FROM entity_changes ec2
    WHERE ec2.entity_id = ec.entity_id
      AND ec2.old_phase = 'Accept'
      AND ec2.new_phase = 'Review'
      AND ec2.resolved_datetime > ec.resolved_datetime
);

逻辑说明 #

1. 外层查询先筛选出所有发生过Accept变更的entity_id；
2. 通过NOT EXISTS子查询，排除那些在当前Accept变更之后，存在从Accept到Review后续变更的实体；
3. 用DISTINCT确保最终结果中每个entity_id只出现一次。

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