如何在systemid=1记录数>5时保留最新3条并删除旧记录

先明确你的需求场景：

给定数据表（原始数据）：

id systemid value
1 1 0
2 1 1
3 1 3
4 1 4
6 1 9
8 1 10
9 1 11
10 1 12

你需要：仅当某个systemid的记录数大于5时，保留该systemid下最新的3条记录（按id倒序，id越大视为越新），删除其余旧记录，最终得到这样的结果：

id systemid value
8 1 10
9 1 11
10 1 12

通用解决方案（支持窗口函数的数据库：PostgreSQL、MySQL 8.0+、SQL Server等） #

现在主流数据库都支持窗口函数，用这个方法最直观高效：

1. 查询出需要保留的记录

如果只是想筛选出要保留的数据而不修改原表，用这个查询：

WITH ranked_records AS (
    SELECT 
        *,
        -- 给每个systemid分组内的记录按id倒序排号，最新的排第1
        ROW_NUMBER() OVER (PARTITION BY systemid ORDER BY id DESC) AS rn,
        -- 统计每个systemid的总记录数
        COUNT(*) OVER (PARTITION BY systemid) AS total_count
    FROM your_table_name -- 替换成你的实际表名
)
SELECT id, systemid, value
FROM ranked_records
-- 只保留总记录数>5且排号在前3的记录
WHERE total_count > 5 AND rn <= 3
ORDER BY id;

2. 删除多余的旧记录

如果要直接在原表中删除不需要的记录，用这个语句：

WITH ranked_records AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY systemid ORDER BY id DESC) AS rn,
        COUNT(*) OVER (PARTITION BY systemid) AS total_count
    FROM your_table_name
)
DELETE FROM your_table_name
WHERE id IN (
    SELECT id 
    FROM ranked_records 
    -- 找出总记录数>5且排号超过3的记录（也就是要删除的旧记录）
    WHERE total_count > 5 AND rn > 3
);

兼容旧版本MySQL（5.x及以下，不支持窗口函数） #

如果你的数据库是旧版MySQL，没法用窗口函数，试试这个关联查询的方法：

-- 删除多余记录
DELETE t1
FROM your_table_name t1
-- 先关联找出记录数>5的systemid
JOIN (
    SELECT systemid, COUNT(*) AS total_count
    FROM your_table_name
    GROUP BY systemid
    HAVING total_count > 5
) t2 ON t1.systemid = t2.systemid
-- 统计当前记录在同systemid中比它新（id更大）的数量，超过2个就说明它不在最新3条里
WHERE (
    SELECT COUNT(*) 
    FROM your_table_name t3 
    WHERE t3.systemid = t1.systemid AND t3.id >= t1.id
) > 3;

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