SQL查询：获取当前非活跃项目末次活跃后的首次非活跃时间

思路拆解 #

1. 锁定当前非活跃项目：先筛选出最新状态为I（非活跃）的项目，排除像项目2这类最新状态是A的项目。
2. 定位末次活跃节点：对符合条件的项目，找到它们最后一次处于A（活跃）状态的日期。
3. 抓取首次转非活跃记录：在末次活跃日期之后，提取该项目第一次切换为I的记录。

SQL 实现 #

假设表名为project_status，字段对应project_id（项目编号）、status（活跃/非活跃）、change_date（日期），以下是通用SQL语句：

WITH latest_status AS (
    -- 筛选当前状态为I的项目
    SELECT project_id
    FROM (
        SELECT 
            project_id,
            status,
            ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY change_date DESC) AS rn
        FROM project_status
    ) t
    WHERE rn = 1 AND status = 'I'
),
last_active AS (
    -- 获取每个目标项目的最后一次活跃日期
    SELECT 
        project_id,
        MAX(change_date) AS last_active_date
    FROM project_status
    WHERE project_id IN (SELECT project_id FROM latest_status)
        AND status = 'A'
    GROUP BY project_id
)
-- 提取末次活跃后首次转非活跃的记录
SELECT 
    ps.project_id,
    ps.status,
    ps.change_date
FROM project_status ps
JOIN last_active la ON ps.project_id = la.project_id
WHERE ps.status = 'I'
    AND ps.change_date > la.last_active_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY ps.project_id ORDER BY ps.change_date ASC) = 1

-- 若数据库不支持QUALIFY（如MySQL），可替换为子查询写法：
-- AND (ps.project_id, ps.change_date) IN (
--     SELECT project_id, MIN(change_date)
--     FROM project_status
--     WHERE project_id IN (SELECT project_id FROM latest_status)
--         AND status = 'I'
--         AND change_date > (SELECT last_active_date FROM last_active WHERE project_id = project_status.project_id)
--     GROUP BY project_id
-- )

结果验证 #

执行后将返回符合需求的记录：

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