如何用SQL窗口函数ROW_NUMBER实现分组内去重

SQL中的ROW_NUMBER()函数常被误会成去重利器，但它其实只负责给行打序号——真正要删除重复行，还得靠外层筛选，比如 WHERE rn = 1。这个道理初看简单，实操时却容易踩坑，特别是排序不稳定或数据库兼容性问题。下面就把这块讲透。

一句话结论：ROW_NUMBER() 不能直接去重，它只是给每行分配一个递增序号；真正去重得靠外部查询，比如 WHERE rn = 1。

为什么 ROW_NUMBER() 本身不等于去重

因为它只是按照你指定的排序规则，给每一行分配一个唯一且递增的数字。哪怕两行数据完全一模一样，只要排序键（ORDER BY）能区分它们——或者排序本身不确定——ROW_NUMBER() 就会分出 1、2、3…… 所以必须配合子查询或 CTE，再过滤出序号为 1 的行。

常见错误场景：SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) FROM orders; 这条语句只是多了一列序号，总行数一条没少。是不是很迷惑人？

标准写法：用 CTE 或子查询 + WHERE 筛选首行

这是最常用也最容易理解的模式。核心思路是：先在每组内算出序号，然后在外层只取 rn = 1 的记录。

• 如果想保留最新一条记录：用 ORDER BY updated_at DESC，然后取 rn = 1
• 如果想保留最早一条：用 ORDER BY created_at ASC
• 注意：如果排序字段里有 NULL，不同数据库行为不同（比如 PostgreSQL 默认把 NULL 排在最前，MySQL 则可能放在最后）。建议显式写 NULLS LAST 或 NULLS FIRST（PostgreSQL/Oracle 支持；MySQL 8.0+ 不支持该语法，需用 IS NULL 模拟）

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id 
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM users
)
SELECT id, user_id, email, updated_at
FROM ranked
WHERE rn = 1;

和 DISTINCT、GROUP BY 的关键区别

DISTINCT 是基于整行值去重，GROUP BY 必须搭配聚合函数。而 ROW_NUMBER() 方案的好处是：能「保留原始行的任意字段」——只要排序逻辑设计合理，就能挑出你想要的那条完整记录。

举个例子：按 user_id 分组，取 updated_at 最新的那条，同时还要拿到它的 status、source、id 等字段。GROUP BY 做不到这点（除非把所有字段都写进 GROUP BY，那就失去了去重的意义）。

性能方面：ROW_NUMBER() 需要排序，大数据量时务必关注 PARTITION BY 和 ORDER BY 字段是否有索引。MySQL 8.0+、PostgreSQL、SQL Server 都支持该函数，但 SQLite 目前（3.45 版本）仍不支持窗口函数。

兼容性陷阱：某些还在用旧版 MySQL 的同学可能误以为开窗函数能用，结果报错 ERROR 1064: You ha ve an error in your SQL syntax ——其实就是版本太低，升级到 8.0 以上才能用。

容易被忽略的排序稳定性问题

当 PARTITION BY 内多行的排序字段值完全相同（比如同一个 user_id 下几条记录的 updated_at 都是 '2024-01-01 00:00:00'），ROW_NUMBER() 的结果是不确定的——数据库可能每次返回不同的“首行”。

解决办法只有一个：在 ORDER BY 里补一个能保证唯一性的字段（比如主键 id），否则去重结果不可复现。

错误写法：ORDER BY updated_at DESC → 同时间戳下哪条被选中纯属随机。
正确写法：ORDER BY updated_at DESC, id DESC → 时间相同时，取 id 最大的那条，结果稳定可预期。