如何利用SQL窗口函数辅助完成复杂的删除逻辑

窗口函数不能直接用于DELETE的WHERE子句，必须通过CTE或子查询先计算行号，再在外层删除rn>1的重复行；PARTITION BY定义重复逻辑，ORDER BY决定保留最新或最旧记录。

窗口函数本身不能直接删除数据

SQL 的 DELETE 语句不支持窗口函数，所以你不能写 DELETE FROM t WHERE ROW_NUMBER() OVER (...) = 1 这类语句——会报错 Windowed functions can only appear in the SELECT or ORDER BY clauses。窗口函数只能出现在 SELECT、ORDER BY 或 GROUP BY（某些数据库）中，不能用于 WHERE 或 DELETE 的过滤条件。

真正可行的路径是：先用窗口函数识别出要删的行，再把结果作为子查询或 CTE 提供给 DELETE 使用。

用 CTE + ROW_NUMBER() 删除重复行保留最新一条

这是最常见场景：表里有重复业务键（如 user_id），想按时间字段（如 updated_at）留最新的一条，删其余。

• 必须用 WITH 定义 CTE，在里面算 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC)
• DELETE 要基于 CTE 的别名（PostgreSQL/SQL Server 支持；MySQL 8.0+ 要用 DELETE ... FROM cte JOIN table 写法）
• 注意排序方向：DESC 才能让最新记录排第 1 名，否则会误删
• PostgreSQL 示例：

WITH ranked AS (  SELECT id, ROW_NUMBER() OVER (    PARTITION BY user_id ORDER BY updated_at DESC  ) rn  FROM users)DELETE FROM usersUSING rankedWHERE users.id = ranked.id AND ranked.rn > 1;

MySQL 8.0 中需绕过 CTE 直接删除的限制

MySQL 不允许在 DELETE 中直接引用同一个表的 CTE（会报 You can't specify target table for update in FROM clause）。必须用中间包装。

• 方案一：用派生表（子查询套一层）
• 方案二：用临时表存下要删的 id 列表
• 推荐方案一，避免显式建临时表
• 示例（删除重复邮箱中创建时间较早的）：

DELETE FROM usersWHERE id IN (  SELECT id FROM (    SELECT id,      ROW_NUMBER() OVER (        PARTITION BY email ORDER BY created_at ASC      ) rn    FROM users  ) t WHERE t.rn > 1);

注意最外层必须有 SELECT id FROM ( ... ) t 这层包装，否则 MySQL 报错。

性能和事务安全提醒

窗口函数本身不慢，但搭配 DELETE 时容易忽略两点：

• 大表上执行前务必确认 PARTITION BY 字段有索引，否则 ROW_NUMBER() 排序成本极高
• DELETE 是 DML 操作，会锁行（甚至锁表），建议在低峰期执行，或分批次删（比如加 LIMIT 1000 并循环）
• 永远先用 SELECT 验证 CTE 或子查询结果是否符合预期，例如：SELECT * FROM (CTE) WHERE rn > 1
• 某些旧版数据库（如 PostgreSQL USING 语法支持不一致，生产前要在同版本环境实测

窗口函数只是“找人”的工具，真正动手删的还是 DELETE —— 找得准不准，全看 PARTITION BY 和 ORDER BY 写得对不对，而不是函数本身多厉害。