PostgreSQL 14：按分组保留最大值记录的DELETE语句问题及解决

错误原因分析 #

你写的DELETE语句存在两个核心问题：

• 语法错误：WHERE子句中不能直接使用聚合函数count(code)，聚合函数需要搭配GROUP BY放在HAVING子句中使用
• 逻辑错误：子查询未按name分组，只能计算全局最大值，无法实现每个name分组保留最大值的需求

正确解法 #

以下两种方法均可实现需求，推荐使用窗口函数的方式，逻辑更清晰高效：

方法1：关联子查询

DELETE FROM code c
WHERE EXISTS (
    SELECT 1
    FROM code c2
    WHERE c2.name = c.name
    GROUP BY c2.name
    HAVING c.value < MAX(c2.value)
);

方法2：窗口函数（推荐）

利用ROW_NUMBER()窗口函数给每个分组内的记录按value降序排名，删除排名大于1的记录：

WITH ranked_codes AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY value DESC) AS rn
    FROM code
)
DELETE FROM code
WHERE id IN (SELECT id FROM ranked_codes WHERE rn > 1);

验证结果 #

执行删除后，查询表数据：

SELECT name, value FROM code ORDER BY name;

得到预期结果：

name  | value
------+-------
name  | 3
name1 | 3
name2 | 3

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