SQL Server实现查询每个分组的前N条记录

前言

在SQL数据库中，我们常常会遇到查询每个分组的前N条记录的需求。例如，我们现在有一个销售订单表，需要查询每个客户的最近5笔订单记录。本文将介绍如何利用SQL Server实现这一需求。

使用ROW_NUMBER()函数进行分组排序

ROW_NUMBER()函数的基本用法

ROW_NUMBER()函数是一种窗口函数，它可以为结果集中的每一行分配一个唯一的序号。

我们可以通过在SELECT语句中使用ROW_NUMBER()函数来为每个分组的记录排序。例如，以下代码将OrderDate列按降序排序，并为每个CustomerID分组分配一个唯一的序号。

SELECT CustomerID, OrderID, OrderDate,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) as RowNum
FROM Orders

上面的代码中，PARTITION BY子句指定要分组的列，ORDER BY子句指定要排序的列和排序顺序。

该查询将返回如下结果：

CustomerID | OrderID |  OrderDate | RowNum
------------------------------------------
    A     |   1001  | 2022-01-01 |   1   
    A     |   1002  | 2022-01-02 |   2   
    A     |   1003  | 2022-01-03 |   3   
    A     |   1004  | 2022-01-04 |   4   
    A     |   1005  | 2022-01-05 |   5   
    B     |   1006  | 2022-01-06 |   1   
    B     |   1007  | 2022-01-07 |   2   
    B     |   1008  | 2022-01-08 |   3   
    B     |   1009  | 2022-01-09 |   4   
    B     |   1010  | 2022-01-10 |   5   

可以看到，该查询为每个CustomerID分组分配了一个唯一的RowNum序号。

查询每个分组的前N条记录

在上面的代码中，我们已经为每个CustomerID分组排序，并为每个分组分配了一个唯一的RowNum序号。现在，我们只需要在查询结果中过滤出每个分组的前N条记录即可。

SELECT CustomerID, OrderID, OrderDate
FROM (
    SELECT CustomerID, OrderID, OrderDate,
        ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) as RowNum
    FROM Orders
) as T
WHERE T.RowNum <= 5

上面的代码中，我们将上一步查询的结果作为子查询，并使用WHERE子句筛选出RowNum<=5的记录。这样就能得到每个客户的最近5笔订单记录了。

使用OUTER APPLY子句进行分组排序

OUTER APPLY子句的基本用法

OUTER APPLY子句也可以实现分组排序的功能。

例如，以下代码将OrderDate列按降序排序，并为每个CustomerID分组返回前5条记录。

SELECT CustomerID, OrderID, OrderDate
FROM Orders as A
OUTER APPLY (
    SELECT TOP 5 OrderDate
    FROM Orders as B
    WHERE A.CustomerID = B.CustomerID
    ORDER BY OrderDate DESC
) as C

上面的代码中，OUTER APPLY子句的作用是将子查询的结果按照主查询的行数进行展开，形成一个新的结果集。子查询的结果包含每个CustomerID分组的前5个OrderDate值。

说明OUTER APPLY子句的执行顺序

OUTER APPLY子句的执行顺序是从左到右，先执行主查询，再执行子查询。

在上面的代码中，主查询先从Orders表中获取所有列的值。然后，对于每一行，OUTER APPLY子句都会执行一次子查询。子查询中过滤出与主查询中的CustomerID相同的记录，并按OrderDate降序排序，然后返回前5个OrderDate值。

子查询返回的结果将展开到主查询的每一行中，只保留了子查询中的OrderDate列，其他列的值将被自动设置为NULL。

查询每个分组的前N条记录

我们可以在子查询中使用TOP子句来返回每个分组的前N条记录。

SELECT CustomerID, OrderID, OrderDate
FROM Orders as A
OUTER APPLY (
    SELECT TOP 5 OrderDate
    FROM Orders as B
    WHERE A.CustomerID = B.CustomerID
    ORDER BY OrderDate DESC
) as C
WHERE C.OrderDate IS NOT NULL

在上面的代码中，只保留了子查询中的OrderDate列的值不为NULL的记录。这样就能得到每个客户的最近5笔订单记录了。

总结

本文介绍了如何使用SQL Server实现查询每个分组的前N条记录。可以使用ROW_NUMBER()函数或OUTER APPLY子句进行分组排序，并通过子查询返回每个分组的前N条记录。

在使用ROW_NUMBER()函数时，要注意在子查询中包含要查询的所有列。在使用OUTER APPLY子句时，要将子查询的结果展开到主查询的每一行中，并使用WHERE子句过滤出目标记录。