详细介绍：MySQL 排名“三剑客”：ROW_NUMBER, RANK, DENSE_RANK 深度解析与实战

想象一下，你需要为每个部门的员工按工资进行排名，或者找出每个类别下销量最高的前 3 个商品。在 SQL 中，如何优雅地实现这些“分组排名”的需求？

在窗口函数 (Window Functions) 出现之前，这往往需要复杂的自连接或子查询，代码冗长且性能低下。幸运的是，现代 SQL 提供了强大的排名函数：ROW_NUMBER(), RANK(), 和 DENSE_RANK()。

这三者都能为你生成排名序号，但它们在处理**“并列” (Ties)** 情况时，行为截然不同。本文将带你深入这“三剑客”的内部，通过清晰的对比、实例和流程图，助你彻底掌握它们的区别，并在实战中运用自如。

1. 窗口函数基础：OVER (PARTITION BY ... ORDER BY ...)

在深入这三个函数之前，必须先理解它们赖以生存的基础——窗口函数的 OVER 子句。

• PARTITION BY <...> (分区): 类似于 GROUP BY，它将数据划分成不同的“窗口”或“分区”。后续的排名将在每个分区内部独立进行。如果省略，则整个结果集视为一个分区。
• ORDER BY <...> (排序):对于排名函数，此子句是必需的。它规定了在每个分区内部，按照哪个字段、以何种顺序（ASC/DESC）来进行排名。

一个形象的比喻：
PARTITION BY 就像把全校学生按“班级”分开，而 ORDER BY 则是规定在每个班级内部，按“成绩”从高到低排座次。

2. 三大排名函数详解

A. ROW_NUMBER() - 连续不重复的“行号”

• 核心逻辑: 为分区内的每一行分配一个唯一的、连续递增的整数。
• 并列处理:无视并列。即使两行的排序字段值完全相同，它们也会获得不同且连续的排名序号（具体哪个在前可能取决于数据库内部实现或额外的排序条件）。
• 类比: 就像给冲过终点线的运动员依次颁发 1, 2, 3, 4… 号码牌，即使有两人同时冲线，也会严格区分先后（可能是根据冲线瞬间的微小差异），号码牌绝不重复，也绝不跳跃。

B. RANK() - 标准并列排名（跳跃式）

• 核心逻辑: 根据排序字段的值进行排名。
• 并列处理: 排序字段值相同的行，获得相同的排名。
• 关键特点: 如果出现并列，后续的排名将会跳跃。例如，如果有 2 个人并列第 1，那么下一个人的排名将是第 3。
• 类比: 典型的“奥林匹克排名”。如果有两名选手并列获得银牌（排名 2），那么就不会有铜牌（排名 3），下一位选手的排名将是第 4。

C. DENSE_RANK() - 密集并列排名（无间隙）

• 核心逻辑: 根据排序字段的值进行排名。
• 并列处理: 排序字段值相同的行，获得相同的排名。
• 关键特点: 如果出现并列，后续的排名不会跳跃，而是紧密连接。例如，如果有 2 个人并列第 1，那么下一个人的排名将是第 2。
• 类比: 学校里的班级排名。允许多人并列第一名，而紧随其后的就是第二名，排名是连续的。

3. 实战对比：学生成绩排名

假设我们有一张 student_scores 表：

student_id	subject	score
1	Math	95
2	Math	90
3	Math	90
4	Math	85
5	English	92
6	English	92
7	English	88

我们想为每个科目 (subject) 内部的学生按成绩 (score) 从高到低排名。

SQL 查询：

SELECT
student_id,
subject,
score,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) as row_num_rank,
RANK()       OVER (PARTITION BY subject ORDER BY score DESC) as standard_rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) as dense_rank
FROM
student_scores;

查询结果：

student_id	subject	score	row_num_rank	standard_rank	dense_rank
5	English	92	1	1	1
6	English	92	2	1	1
7	English	88	3	3	2
1	Math	95	1	1	1
2	Math	90	2	2	2
3	Math	90	3	2	2
4	Math	85	4	4	3

结果分析 (一目了然)：

• row_num_rank: 严格按照 1, 2, 3… 编号，即使分数相同（如 English 科目的 5 和 6 号学生）。
• standard_rank: 分数相同的获得相同排名（English 的 5, 6 都是 1；Math 的 2, 3 都是 2）。但后续排名跳跃了（English 没有排名 2，Math 没有排名 3）。
• dense_rank: 分数相同的获得相同排名，但后续排名是连续的（English 的 7 号学生是第 2 名，Math 的 4 号学生是第 3 名）。

4. 高阶用法与场景选择

理解了基础区别后，我们来看如何在实际场景中选择和应用它们。

场景一：获取分组 Top N (最常用)

• 需求: 找出每个部门工资最高的 3 名员工。
• 选择:ROW_NUMBER()。因为我们通常只需要一个明确的 Top N 列表，不关心并列情况如何处理（或者说，即使并列，我们也只需要取够 N 个即可）。
• 实现 (使用子查询或 CTE):

  WITH RankedEmployees AS (
  SELECT
  emp_id,
  dept_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rn
  FROM employees
  )
  SELECT emp_id, dept_id, salary
  FROM RankedEmployees
  WHERE rn <= 3;

场景二：数据去重

• 需求: 对于 (user_id, login_time) 表，保留每个用户最新的一次登录记录。
• 选择:ROW_NUMBER()。我们需要为每个 user_id 分区内的记录按 login_time 降序编号，然后只取编号为 1 的那条记录。
• 实现:

  WITH LatestLogins AS (
  SELECT
  id, user_id, login_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) as rn
  FROM login_logs
  )
  DELETE FROM login_logs
  WHERE id IN (SELECT id FROM LatestLogins WHERE rn > 1); -- 或者 SELECT * ... WHERE rn = 1

场景三：计算带有并列名次的排名 (标准排名)

• 需求: 生成运动会比赛成绩排名榜，允许并列名次，且后续名次跳跃。
• 选择:RANK()。这完全符合“奥林匹克排名”的规则。
• 实现:

  SELECT
  athlete_name,
  score,
  RANK() OVER (ORDER BY score DESC) as final_rank
  FROM competition_results;

场景四：计算无间隙的排名或等级

• 需求: 根据用户积分，将其划分为不同的等级（例如，前 10% 为钻石，11%-30% 为黄金…），或者统计某个商品在同类商品中的“销量排名”（允许并列，且排名连续）。
• 选择:DENSE_RANK()。它能提供连续的排名，便于后续基于排名的区间判断或统计。
• 实现:

  SELECT
  product_name,
  category,
  sales_count,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY sales_count DESC) as rank_in_category
  FROM product_sales;

5. 性能考量

窗口函数，特别是排名函数，通常需要在分区内进行排序操作。为了获得最佳性能：

• 为 PARTITION BY 和 ORDER BY 的列建立合适的索引。 复合索引通常效果最佳。
• 在超大规模数据集上，窗口函数的计算可能会消耗较多内存和 CPU。考虑是否可以通过其他方式（如在应用层处理）来优化。

总结：如何选择？一张图搞定

选择流程图 (修正版):

核心选择依据：如何处理并列（Ties）？

• 不需要并列，只要唯一序号？ -> ROW_NUMBER()
• 需要并列，且并列后跳过名次？ -> RANK()
• 需要并列，且并列后名次连续？ -> DENSE_RANK()