SQL场景：如何筛选前一状态为inprog且当前状态非active的SID

方法一：使用窗口函数（推荐，简洁高效） #

利用LAG()窗口函数直接获取每个状态的前序状态，实现逻辑如下：

SELECT DISTINCT sid
FROM (
    SELECT 
        sid,
        status,
        -- 按sid分组、时间升序，获取当前状态的前一个状态
        LAG(status) OVER (PARTITION BY sid ORDER BY status_dt) AS prev_status
    FROM t1
) AS status_sequence
-- 筛选前一状态是'inprog'且当前状态不是'active'的记录
WHERE prev_status = 'inprog' AND status != 'active';

方法二：自关联表（兼容不支持窗口函数的数据库） #

如果你的数据库不支持窗口函数，可以通过自关联匹配紧邻的前后状态：

SELECT DISTINCT t_current.sid
FROM t1 AS t_current
JOIN t1 AS t_prev 
    ON t_current.sid = t_prev.sid 
    AND t_current.status_dt > t_prev.status_dt
    -- 确保两个状态之间无中间记录，保证是紧邻的前后序
    AND NOT EXISTS (
        SELECT 1 FROM t1 AS t_mid 
        WHERE t_mid.sid = t_current.sid 
        AND t_mid.status_dt > t_prev.status_dt 
        AND t_mid.status_dt < t_current.status_dt
    )
WHERE t_prev.status = 'inprog' AND t_current.status != 'active';

两种方法都会返回符合条件的sid列表，比如示例数据中会返回451（它在'inprog'之后的状态是'hold'，不符合'active'要求）。

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