第 12.6 课 · 阅读时间：约 7 分钟

每组前 N 行（英文为“greatest N per group”）是指在每个分组中挑出最好的几行：每家门店消费最多的三位客户、每个分类中最长的两部电影、每位客户的最近一笔订单。它在报表和 SQL 面试中经常出现。在本课中，你将用 ROW_NUMBER() 和 PARTITION BY 解决这个问题，看到并列如何改变结果，并学习两种替代方案：相关子查询和 LATERAL 连接。

SQL 每组前 N 行

ORDER BY ... LIMIT 3 返回的是整个结果的前三行，而不是每组的前三行。分组也无济于事：GROUP BY 会把每组压缩成一行，而我们需要每组保留多行原始数据。解决办法是在每个分组内为行编号，然后保留前 N 行。

示例在 Sakila 示例数据库（MySQL 8.0）上运行。

题目

找出两家门店中各自付款最多的三位客户。

首先计算每位客户的付款总额：

SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
       SUM(p.amount) AS total_paid
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
ORDER BY total_paid DESC
LIMIT 3;

结果：KARL SEAL（门店 2，221.55）、ELEANOR HUNT（门店 1，216.54）、CLARA SHAW（门店 1，195.58）——这是整体的前三名，而不是每家门店的前三名。

解法 1：ROW_NUMBER 配合 PARTITION BY

PARTITION BY store_id 让每家门店重新开始编号；ORDER BY total_paid DESC 把编号 1 给付款最多的客户。窗口函数不能用在同一查询的 WHERE 中，所以过滤条件放在外层查询里：

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
           SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
),
ranked AS (
    SELECT store_id, first_name, last_name, total_paid,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS rn
    FROM customer_total
)
SELECT store_id, first_name, last_name, total_paid, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;

结果：6 行，每家门店三行。

并列：ROW_NUMBER、RANK 还是 DENSE_RANK

RHONDA KENNEDY 和 MARION SNYDER 的付款金额完全相同，但 ROW_NUMBER 给了她们不同的编号。出现并列时，这三个函数回答的是不同的问题。来看两个分类中最长的电影，这里的时长经常重复：

WITH ranked AS (
    SELECT c.name AS category, f.title, f.length,
           ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS row_num,
           RANK()       OVER (PARTITION BY c.name ORDER BY f.length DESC) AS rnk,
           DENSE_RANK() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS dense_rnk
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
)
SELECT category, title, length, row_num, rnk, dense_rnk
FROM ranked
WHERE category IN ('Action', 'Comedy') AND dense_rnk <= 3
ORDER BY category, row_num;

结果：9 行；在 Action 分类中，有两部电影并列最长，都是 185 分钟。

• ROW_NUMBER() <= N：每组恰好 N 行；相等的值之间如何取舍是任意的。在 ORDER BY 中加上用于打破并列的列，例如 f.length DESC, f.title，结果才会稳定。
• RANK() <= N：所有能进入前 N 名的行，因此一个分组可能返回超过 N 行。
• DENSE_RANK() <= N：取值位于前 N 个不同值的行。

面试时要先问清楚题意：“前 3 部电影”通常对应 ROW_NUMBER，“时长排名前 3 的电影”对应 DENSE_RANK。

解法 2：相关子查询

在没有窗口函数时（MySQL 8.0 之前），如果同一分组中比某行值大的行少于 N 行，那么这一行就属于该组的前 N 名：

SELECT c.name AS category, f.title, f.length
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
  AND (
      SELECT COUNT(*)
      FROM film f2
      JOIN film_category fc2 ON fc2.film_id = f2.film_id
      WHERE fc2.category_id = fc.category_id
        AND f2.length > f.length
  ) < 2
ORDER BY f.length DESC;

结果：2 行，DARN FORRESTER 和 WORST BANGER，即 Action 中没有更长电影的两部影片；效果相当于 RANK() <= 2。

子查询会对每一行执行一次，所以在大表上更慢，但它适用于任何数据库。

解法 3：LATERAL 连接

LATERAL 子查询可以引用 FROM 中位于它之前的表的列。这样就能直接写出“对每个分类，取其最长的 2 部电影”，并在子查询内部使用 ORDER BY 和 LIMIT：

SELECT c.name AS category, top_films.title, top_films.length
FROM category c
JOIN LATERAL (
    SELECT f.title, f.length
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    WHERE fc.category_id = c.category_id
    ORDER BY f.length DESC, f.title
    LIMIT 2
) AS top_films ON TRUE
WHERE c.name IN ('Action', 'Comedy')
ORDER BY category, top_films.length DESC;

结果：4 行：Action 中的 DARN FORRESTER 和 WORST BANGER，Comedy 中的 CONTROL ANTHEM 和 SATURN NAME。

PostgreSQL 和 MySQL 8.0.14+ 支持 LATERAL，MariaDB 不支持；SQL Server 用 CROSS APPLY 实现同样的思路。如果分组列和排序列上有索引，当分组很大而 N 很小时，这往往是最快的方法。

练习

• 每月热门上车区域
• 各部门薪水最高的员工
• 按受欢迎程度排名的电影

要点：

• LIMIT 和 GROUP BY 无法每组返回多行；应该在每个分组内为行编号。
• ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) 加上外层查询中的 WHERE rn <= N 是标准解法。
• 出现并列时，ROW_NUMBER 恰好返回 N 行，RANK 可能返回更多行，DENSE_RANK 返回前 N 个不同值对应的行。
• 相关子查询可以在没有窗口函数的情况下解题，但速度更慢。
• JOIN LATERAL（SQL Server 中为 CROSS APPLY）配合 ORDER BY ... LIMIT N 是一种可读性好、通常也很快的替代方案。

常见问题

如何在 SQL 中选出每组的前 N 行？

在 CTE 或子查询中用 ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) 为每组的行编号，然后在外层查询中保留编号不超过 N 的行。

为什么不能在 WHERE 中使用窗口函数？

窗口函数在 WHERE、GROUP BY 和 HAVING 之后、SELECT 阶段才计算。要按窗口函数的结果过滤，就要在 CTE 或子查询中计算它们，再在外层查询中过滤。有些数据库（Snowflake、DuckDB、BigQuery）为此提供了 QUALIFY 子句。

在求前 N 名时，ROW_NUMBER、RANK 和 DENSE_RANK 有什么区别？

ROW_NUMBER 给每行一个唯一编号，因此恰好返回 N 行，并任意打破并列。RANK 给相等的值相同的名次并跳过后续编号，因此可能返回超过 N 行。DENSE_RANK 不跳号，会返回前 N 个不同值对应的所有行。

如何获取每位客户的最新一行？

这是每组取第 1 行：ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) 加上 WHERE rn = 1。在 PostgreSQL 中，可以用更简短的 SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC。