MySQL 8.0如何实现分组内排序？窗口函数怎么取代传统关联子查询

MySQL 8.0版本新增的窗口函数特性，让分组内排序这类常见需求的实现方式发生了很大变化，开发者可以摆脱过去复杂的关联子查询写法，用更简洁的逻辑完成需求。

传统关联子查询实现分组内排序

在MySQL 8.0之前，要实现按某个字段分组，再对组内数据按其他字段排序的需求，通常需要使用关联子查询。比如我们有订单表order_info，结构如下：

如果要查询每个用户金额最高的前3个订单，传统关联子查询的写法如下：

SELECT 
  o1.user_id,
  o1.order_amount,
  o1.create_time
FROM order_info o1
WHERE (
  SELECT COUNT(*)
  FROM order_info o2
  WHERE o2.user_id = o1.user_id
    AND o2.order_amount >= o1.order_amount
) <= 3
ORDER BY o1.user_id, o1.order_amount DESC;

这种写法的逻辑是，对每个订单o1，统计同用户下金额大于等于它的订单数量，数量不超过3的就保留，从而实现每个用户组内按金额降序取前3。但这种写法存在明显缺点：子查询会多次执行，数据量大的时候性能很差，而且代码逻辑不够直观，维护成本高。

窗口函数实现分组内排序

MySQL 8.0支持的窗口函数可以直接在查询结果集上进行计算，不需要关联子查询。常用的分组排序窗口函数有三个：

• ROW_NUMBER()：为每组内的行分配连续的序号，相同排序值会分配不同的序号
• RANK()：为每组内的行分配序号，相同排序值序号相同，后续序号会跳过重复的数量
• DENSE_RANK()：为每组内的行分配序号，相同排序值序号相同，后续序号不会跳过

基础语法说明

窗口函数的基本语法是：

函数名() OVER (
  PARTITION BY 分组字段
  ORDER BY 排序字段 [ASC|DESC]
) AS 别名

其中PARTITION BY用来指定分组的字段，相当于GROUP BY的作用，但是不会合并行；ORDER BY用来指定组内的排序规则。

实现分组内取前N名的需求

还是上面的订单表需求，用ROW_NUMBER()实现每个用户金额最高的前3个订单，写法如下：

SELECT 
  user_id,
  order_amount,
  create_time
FROM (
  SELECT 
    user_id,
    order_amount,
    create_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_amount DESC
    ) AS rn
  FROM order_info
) t
WHERE rn <= 3
ORDER BY user_id, order_amount DESC;

这个查询先通过子查询给每个用户的订单按金额降序分配序号，然后外层查询过滤出序号小于等于3的记录，逻辑清晰，执行效率也比关联子查询高很多。

不同排序函数的差异示例

如果订单金额存在相同的情况，三个函数的表现不同。假设用户1有三个订单，金额分别是100、100、80，那么：

SELECT 
  user_id,
  order_amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS row_num,
  RANK() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS dense_rank_num
FROM order_info
WHERE user_id = 1;

查询结果会是：

开发者可以根据实际业务需求选择合适的排序函数，比如如果要严格取前3个不同的金额档位，就用DENSE_RANK()。

两种方案对比

对比传统关联子查询和窗口函数方案，差异主要有以下几点：

• 代码可读性：窗口函数逻辑更直观，不需要嵌套多层子查询，维护成本更低
• 执行性能：窗口函数只需要扫描一次表，关联子查询需要多次执行子查询，数据量大时窗口函数性能优势明显
• 功能灵活性：窗口函数还支持很多其他计算，比如累计求和、移动平均等，适用场景更广泛

注意：窗口函数是MySQL 8.0及以上版本才支持的特性，如果使用的MySQL版本低于8.0，无法使用这种方式，只能选择传统关联子查询或者其他替代方案。

实际业务应用示例

再举一个实际场景的例子：学生成绩表score，包含student_id（学生ID）、subject（科目）、score（分数）字段，要查询每个科目分数排名前2的学生信息，使用窗口函数的写法如下：

SELECT 
  student_id,
  subject,
  score
FROM (
  SELECT 
    student_id,
    subject,
    score,
    DENSE_RANK() OVER (
      PARTITION BY subject
      ORDER BY score DESC
    ) AS subject_rank
  FROM score
) t
WHERE subject_rank <= 2
ORDER BY subject, score DESC;

这个查询可以正确返回每个科目分数最高的两个档位的学生，即使有同分的情况也能正确处理。

MySQL_8.0分组内排序窗口函数关联子查询修改时间：2026-06-24 02:03:49