如何用单条SQL语句实现遍历查询结果删除指定表冗余数据的逻辑

实现逻辑说明 #

你原本的需求是对table_a中符合条件的每个ID，删除table_b中对应关联ID下除了最新50条之外的所有历史记录，不需要循环，直接通过关联子查询或者窗口函数即可单条语句实现。

写法1：兼容低版本数据库（支持基础子查询即可） #

这种写法和你原有循环内的删除逻辑完全一致，只是把遍历ID的逻辑替换成了关联匹配：

DELETE FROM table_b
WHERE 
  -- 限定仅处理table_a中符合筛选条件的ID对应的记录
  table_a_id IN (SELECT id FROM table_a WHERE /* 此处替换为你原有的table_a筛选条件 */)
  AND id < (
    SELECT min(id) 
    FROM (
      SELECT id 
      FROM table_b AS b_inner 
      WHERE b_inner.table_a_id = table_b.table_a_id 
      ORDER BY id DESC 
      LIMIT 50
    ) AS idlist
  );

写法2：支持窗口函数的数据库（MySQL8.0+/PostgreSQL/SQL Server/Oracle等） #

用窗口函数实现逻辑更清晰，执行效率也更高：

DELETE FROM table_b
WHERE id IN (
  SELECT id 
  FROM (
    SELECT 
      b.id,
      -- 按关联的table_a_id分组，按id倒序排列，最新的50条行号为1~50
      ROW_NUMBER() OVER (PARTITION BY b.table_a_id ORDER BY b.id DESC) AS rn
    FROM table_b b
    INNER JOIN table_a a ON b.table_a_id = a.id
    WHERE /* 此处替换为你原有的table_a筛选条件 */
  ) AS temp
  -- 只删除排序在50名之后的旧记录
  WHERE rn > 50
);

注意事项 #

• 执行删除前建议先把DELETE关键字替换为SELECT *，验证返回的待删除记录是否符合预期，避免误删数据
• 数据量较大的场景下，建议给table_b的table_a_id和id字段加联合索引，大幅提升执行效率

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