如何筛选表：获取无前置passed状态的failed记录的f_id与name

先看示例表数据：

f_id | name    | status | date
-----|---------|--------|----------
12   | walnut  | passed | 7/17/2023
13   | beech   | passed | 7/18/2023
12   | walnut  | failed | 7/19/2023
11   | almond  | failed | 7/20/2023
13   | beech   | passed | 7/21/2023

我们要找的是：状态为failed，且对应f_id在这条记录的日期之前完全没出现过passed状态的f_id和name。下面提供两种可行实现方式：

方法1：用NOT EXISTS子查询（最直观） #

直接针对每条failed记录，检查同f_id是否存在更早的passed记录：

SELECT DISTINCT t.f_id, t.name
FROM my_table t
WHERE t.status = 'failed'
  AND NOT EXISTS (
    SELECT 1
    FROM my_table t2
    WHERE t2.f_id = t.f_id
      AND t2.status = 'passed'
      AND t2.date < t.date
  );

• 外层先筛出所有状态为failed的记录
• 子查询验证：同一个f_id下，有没有日期比当前记录更早的passed状态
• 用DISTINCT去重，避免同一个f_id有多条符合条件的failed记录时重复输出

方法2：用窗口函数提前计算最早passed日期 #

通过CTE先给每个f_id算出最早的passed日期，再做筛选：

WITH f_id_status AS (
  SELECT 
    f_id,
    name,
    status,
    date,
    -- 计算该f_id最早出现passed的日期，从未出现则为NULL
    MIN(CASE WHEN status = 'passed' THEN date END) OVER (PARTITION BY f_id) AS earliest_passed_date
  FROM my_table
)
SELECT DISTINCT f_id, name
FROM f_id_status
WHERE status = 'failed'
  AND (earliest_passed_date IS NULL OR date < earliest_passed_date);

• 先通过窗口函数MIN() OVER (PARTITION BY f_id)统计每个f_id最早的passed日期，没有的话值为NULL
• 再筛选出failed记录，同时满足「从未有过passed」或者「这条failed的日期比最早passed日期还早」的条件

两种方法最终都会得到预期结果：

f_id | name
-----|---------
11   | almond

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