PostgreSQL中如何删除指定房间除最新100条外的旧记录

原MySQL的DELETE JOIN写法在PostgreSQL中不兼容，因为两者的DELETE语法实现逻辑不同。以下是两种适配PostgreSQL的可行方案：

方案一：子查询+窗口函数筛选待删除记录 #

利用ROW_NUMBER()窗口函数对指定房间的记录按时间倒序编号，筛选出编号超过100的记录ID执行删除：

DELETE FROM t1
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY room ORDER BY record_date DESC) AS rn
        FROM t1
        WHERE room = 'myroom'
    ) sub_query
    WHERE rn > 100
);

逻辑说明： #

• 最内层子查询：对room='myroom'的记录，按record_date降序排序并分配行号，最新记录行号为1，旧记录行号依次递增
• 中间层筛选出行号大于100的记录ID，这些就是需要清理的旧记录
• 外层DELETE根据筛选出的ID删除对应记录

方案二：使用USING子句关联筛选 #

PostgreSQL的DELETE支持USING子句，可直接关联子查询结果，避免IN子查询的写法：

DELETE FROM t1
USING (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY room ORDER BY record_date DESC) AS rn
    FROM t1
    WHERE room = 'myroom'
) sub_query
WHERE t1.id = sub_query.id AND sub_query.rn > 100;

逻辑说明： #

• USING子句引入带行号的子查询结果，直接与t1表通过id关联
• 仅删除行号大于100的记录，实现保留最新100条的需求

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