如何删除顶部equipstatus为operation的指定行数且保留每组首个operation记录

实现方案（适配支持窗口函数的主流数据库：MySQL8.0+、PostgreSQL、SQL Server等） #

首先明确两个需求的逻辑规则：按时间字段dt升序排序为基准，1. 删除排序后最靠前的N条equipstatus为operation的记录；2. 后续连续出现的operation记录仅保留每组的第一条。

1. 先查询验证预期结果

WITH ranked_equipment AS (
    -- 给所有记录按时间排序，加行号、取上一行的状态
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY dt ASC) AS rn,
        LAG(equipstatus, 1, '') OVER (ORDER BY dt ASC) AS prev_status
    FROM equipment
),
to_delete AS (
    -- 标记需要删除的记录id
    SELECT id FROM ranked_equipment
    WHERE 
        -- 需求1：顶部指定行数（此处N=3匹配样例，可自行修改数值）的operation记录
        (equipstatus = 'operation' AND rn <= 3)
        OR
        -- 需求2：连续operation的非第一条记录
        (equipstatus = 'operation' AND prev_status = 'operation')
)
-- 查询最终保留的记录
SELECT equipstatus AS equip_status, dt, id 
FROM equipment
WHERE id NOT IN (SELECT id FROM to_delete)
ORDER BY dt ASC;

2. 直接执行删除操作

确认查询结果符合预期后，替换最后一段查询语句为删除逻辑即可：

WITH ranked_equipment AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY dt ASC) AS rn,
        LAG(equipstatus, 1, '') OVER (ORDER BY dt ASC) AS prev_status
    FROM equipment
),
to_delete AS (
    SELECT id FROM ranked_equipment
    WHERE 
        (equipstatus = 'operation' AND rn <= 3)
        OR
        (equipstatus = 'operation' AND prev_status = 'operation')
)
DELETE FROM equipment WHERE id IN (SELECT id FROM to_delete);

注：样例测试数据中id为006的dt字段值多输入了一个末尾的0，实际使用时请修正为合法时间格式即可。

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