如何用单条SQL语句实现：表中仅1条记录则删除，多条则保留最新？

需求说明 #

针对bv.profile表中指定user_id的记录，执行以下清理逻辑：

• 若该user_id对应的记录数等于1：删除这条唯一记录
• 若该user_id对应的记录数大于1：仅保留id最大的最新记录，删除其余所有记录

原实现方案 #

原通过后端分两步执行SQL：先查询记录数，再根据结果执行对应删除操作：

-- 第一步：统计目标user_id的记录数
select count(*) from bv.profile where user_id = $1;

-- 第二步：根据统计结果执行删除
-- 当记录数=1时
delete from bv.profile where user_id = $1;

-- 当记录数>1时
delete from bv.profile
where id not in (
  select id
  from bv.profile
  order by id desc
  limit 1
)
and user_id = $1;

你的尝试语句分析 #

你写出的单条SQL逻辑是符合需求的，但存在可以优化的性能点：

delete from bv.profile
where user_id = $1 
and id not in (
  select max(id) from bv.profile
  where user_id = $1
  group by user_id
    having count(*) > 1
);

逻辑上：当目标user_id记录数>1时，子查询返回最大id，删除时排除该id；当记录数=1时，子查询因having count(*) >1无结果，id not in (空)等价于所有行满足条件，会删除唯一记录，完全匹配需求。

但子查询里的group by user_id是冗余的——已经通过where user_id = $1限定了单用户，无需再分组。另外，用NOT IN处理空结果虽然可行，但换成更直观的条件判断，结合索引可以进一步提升性能。

优化后的单条SQL方案 #

推荐两种更高效、可读性更强的写法：

写法一：子查询获取需保留的ID（或空值） #

delete from bv.profile p
using (
  select case when count(*) > 1 then max(id) else null end as keep_id
  from bv.profile
  where user_id = $1
) t
where p.user_id = $1
and (p.id != t.keep_id or t.keep_id is null);

逻辑说明：

• 子查询t根据目标user_id的记录数，返回要保留的id（记录数>1时取max(id)）或空值（记录数=1时）
• 删除条件：当keep_id不为空时，删除所有id不等于keep_id的记录；当keep_id为空时，直接删除该user_id的所有记录（即唯一那条）

写法二：窗口函数分区标记（适用于支持窗口函数的数据库，如PostgreSQL、MySQL 8+） #

delete from bv.profile
where id in (
  select id
  from (
    select id,
           count(*) over (partition by user_id) as total_records,
           row_number() over (partition by user_id order by id desc) as record_rank
    from bv.profile
    where user_id = $1
  ) t
  where total_records = 1 or record_rank > 1
);

逻辑说明：

• 内层子查询给每条记录标记两个值：所属user_id的总记录数total_records，以及按id降序排列的行号record_rank（最新记录的行号为1）
• 外层筛选出两类记录：总记录数为1的所有记录，或行号>1的旧记录，最终删除这些记录

性能优化建议 #

• 给user_id建立索引：create index idx_profile_user_id on bv.profile(user_id);，能快速定位目标user_id的所有记录，大幅提升查询和删除效率
• 优先选择窗口函数写法，逻辑更直观，且在有合适索引的情况下，窗口函数的计算开销极低
• 避免不必要的分组、排序操作，减少子查询的计算量

测试验证 #

用你提供的测试数据验证：

create schema if not exists bv;
create table bv.profile (id bigint primary key, user_id bigint not null);
insert into bv.profile values(2, 100), (1, 101), (3, 100);

• 针对user_id=101（仅1条记录）：执行优化后的SQL后，id=1的记录会被删除
• 针对user_id=100（共2条记录）：执行后仅保留id=3的最新记录，删除id=2的旧记录

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