Oracle SQL如何查询曾经过指定前置状态的目标状态ID记录

核心逻辑 #

要实现「目标状态为d时，必须在d之前的流转中出现过前置状态b，先d后b的场景不满足要求」的筛选，核心判断依据是同一个ID下，前置状态b的变更时间必须早于目标状态d的变更时间。
前提：你的状态流转表需包含3个核心字段：

• id：业务记录唯一标识
• status：状态值
• operate_time：状态变更时间（如果没有时间字段，用自增的流转记录ID替代，数值越大代表记录越新即可）

实现代码 #

写法1：EXISTS关联（可读性高，性能好） #

直接判断是否存在早于当前d状态的b状态记录即可：

SELECT 
    t.id,
    t.status
FROM 你的状态流转表名 t
WHERE t.status = 'd' -- 筛选目标状态为d的记录
AND EXISTS (
    SELECT 1
    FROM 你的状态流转表名 pre
    WHERE pre.id = t.id
      AND pre.status = 'b' -- 匹配指定前置状态b
      AND pre.operate_time < t.operate_time -- 保证b的出现时间早于d，排除反向顺序场景
)

如果需要额外排除「出现d之后又回退到b」的场景，追加如下条件即可：

AND NOT EXISTS (
    SELECT 1
    FROM 你的状态流转表名 after_t
    WHERE after_t.id = t.id
      AND after_t.status = 'b'
      AND after_t.operate_time > t.operate_time
)

写法2：窗口函数（适合批量校验多组状态规则） #

如果需要同时校验多组前置-目标状态，用窗口函数一次性计算每个ID下各状态的首次出现时间再做判断更高效：

WITH status_calc AS (
    SELECT 
        id,
        status,
        operate_time,
        -- 计算每个ID下b状态首次出现的时间
        MIN(CASE WHEN status = 'b' THEN operate_time END) OVER (PARTITION BY id) AS first_b_time,
        -- 计算每个ID下d状态首次出现的时间
        MIN(CASE WHEN status = 'd' THEN operate_time END) OVER (PARTITION BY id) AS first_d_time
    FROM 你的状态流转表名
)
SELECT DISTINCT id, status
FROM status_calc
WHERE status = 'd'
  AND first_b_time IS NOT NULL -- 历史上存在b状态
  AND first_b_time < first_d_time -- b首次出现时间早于d首次出现时间

示例参考 #

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