SQL如何获取当前行在分组中的位置索引 ROW_NUMBER基础

SQL如何获取当前行在分组中的位置索引 ROW_NUMBER基础

ROW_NUMBER() 必须配合 OVER() 使用，否则直接报错

如果你直接写个 ROW_NUMBER() 就想运行，那数据库可不会买账。无论是 MySQL 8.0+ 还是 PostgreSQL，都会直接甩给你一个类似 function row_number() requires a window specification 的错误。原因很简单：它压根就不是一个普通函数，而是一个标准的窗口函数，必须和 OVER() 子句绑定在一起，用来定义排序和分区的逻辑。

所以，它的正确打开方式至少是这样的：ROW_NUMBER() OVER (ORDER BY some_column)。如果不加 PARTITION BY，那就是给全表生成连续编号；加上它，才能实现按分组重置计数的效果。

分组内编号要写对 PARTITION BY，别和 GROUP BY 混用

这里有个关键概念必须厘清：PARTITION BY 是窗口函数内部用来分组的，而 GROUP BY 是聚合语句用来分组的。两者虽然名字里都有“分组”，但完全是两码事，不能互相替代。

一个典型的错误场景是，在 SELECT 里用了 ROW_NUMBER()，同时又写了 GROUP BY。这么干，大概率要么报错，要么得到的结果莫名其妙。因为窗口函数的计算发生在 GROUP BY 聚合之后，那时数据行已经被压缩了，ROW_NUMBER() 自然就失去了用武之地。

• 正确姿势：想实现“每个分类下按创建时间排序编号”，直接用 OVER (PARTITION BY category ORDER BY created_at) 就行。
• 错误示范：SELECT category, ROW_NUMBER() OVER (...) FROM t GROUP BY category —— 这么写，每个分组就只剩一行了，编号还有什么意义呢？
• 版本注意：MySQL 5.7 及更早的版本不支持窗口函数，得升级到 8.0+；SQLite 也得是 3.25 版本之后才支持。

ORDER BY 在 OVER() 里不可省略，否则编号顺序不确定

ROW_NUMBER() 必须知道按什么规则来排座次，所以 OVER () 里不写 ORDER BY 是行不通的。数据库会明确告诉你，比如 PostgreSQL 会报 window function row_number requires an ordering clause。即使你觉得“顺序无所谓，随便给个号就行”，也得显式指定一个确定性的字段，比如主键 ORDER BY id。

实际应用中，还有几个坑得留心：

• 用 ORDER BY RAND() 语法上没问题，但每次执行顺序都变，不适合需要稳定结果的分页或幂等场景。
• 按多个字段排序时，如果前面的字段有重复值（比如好几行都是 status = 'done'），最好在后面补一个唯一字段（如 id），否则编号可能每次都不一样。
• NULL 值的排序位置会影响编号结果。默认情况下，有的数据库（如 PostgreSQL、SQL Server）把 NULL 排在最前，有的（如 MySQL）排在最后。必要时可以使用像 ORDER BY col ASC NULLS LAST 这样的语法（注意，MySQL 不支持这种写法）。

ROW_NUMBER() 和 RANK()、DENSE_RANK() 的编号逻辑差异很关键

这三个函数都能生成序号，但处理“并列”情况的方式截然不同，用错了直接导致业务逻辑出问题。举个例子，假设一个分组里有三行数据，前两行的排序值并列第一：

• ROW_NUMBER()：铁面无私，强制给出不同序号 → 结果会是 1, 2, 3。
• RANK()：允许并列，但会“跳号” → 结果会是 1, 1, 3。
• DENSE_RANK()：允许并列，且“不跳号” → 结果会是 1, 1, 2。

看个典型场景：想找出“每个部门工资最高的员工”。如果用 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)，当最高工资只有一人时没问题；但如果最高工资有多个并列，ROW_NUMBER() 只会挑出其中一个当第一，而 RANK() 才能让所有并列第一的人都拿到序号1。

来看一个取各部门薪资前三名的实际写法示例：

SELECT dept, name, salary, rn
FROM (
  SELECT dept, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn
  FROM employees
) t
WHERE rn <= 3;

这里在按 salary 降序排之后，特意加上了 id 作为第二排序条件。目的就是为了在薪资相同的情况下，有一个稳定的排序依据，避免同一批数据多次执行时，排名结果飘忽不定。