PostgreSQL中获取每个任务ID被删除前的上一状态

假设你的任务表名为tasks，包含以下核心字段：

• task_id：任务唯一ID
• status：任务状态值（含'Deleted'）
• status_updated_at：状态更新时间（用于确定状态变更的先后顺序）

方法1：使用窗口函数（推荐） #

通过窗口函数给每个任务的状态记录按时间倒序排序，直接取Deleted状态的前一条记录：

WITH task_status_ranked AS (
    SELECT
        task_id,
        status,
        ROW_NUMBER() OVER (PARTITION BY task_id ORDER BY status_updated_at DESC) AS rank_num
    FROM tasks
)
SELECT
    task_id,
    status AS previous_status_before_deleted
FROM task_status_ranked
WHERE rank_num = 2;

逻辑说明：

• 按task_id分组后，将每个任务的状态记录按更新时间倒序排列，最终的'Deleted'状态会排在第1位（rank_num=1），它的上一个状态自然就是第2位（rank_num=2）。

方法2：基于Deleted时间关联查询 #

先定位每个任务的Deleted状态更新时间，再查询该时间点之前的最新状态：

SELECT
    t.task_id,
    t.status AS previous_status_before_deleted
FROM tasks t
INNER JOIN (
    SELECT task_id, status_updated_at AS deleted_timestamp
    FROM tasks
    WHERE status = 'Deleted'
) d ON t.task_id = d.task_id
WHERE t.status_updated_at = (
    SELECT MAX(status_updated_at)
    FROM tasks
    WHERE task_id = t.task_id AND status_updated_at < d.deleted_timestamp
);

适配说明：
如果你的表是用自增主键（比如id）来判断状态顺序的，只需把上述语句中的status_updated_at替换为自增字段即可。

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