让我们从基本场景开始。

如果我想从表中获取一些行数，我有两个主要选择：排名函数；或TOP。

Production.TransactionHistory首先，让我们考虑一个特定的整个集合ProductID：

SELECT h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800;

这将返回 418 行，并且该计划显示它检查表中的每一行以查找这一点 - 一个不受限制的聚集索引扫描，带有一个谓词来提供过滤器。797 读到这里，很丑。

所以让我们公平对待它，并创建一个更有用的索引。我们的条件要求在 上进行相等匹配ProductID，然后搜索最近的 by TransactionDate。我们也需要TransactionID返回的，所以让我们一起去：CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory (ProductID, TransactionDate) INCLUDE (TransactionID);。

完成此操作后，我们的计划发生了重大变化，并将读数降至 3。所以我们已经将事情提高了 250 倍左右......

现在我们已经平衡了竞争环境，让我们看看顶级选项 - 排名功能和TOP.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID, ProductID, TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

您会注意到第二个 ( TOP) 查询比第一个查询简单得多，无论是在查询中还是在计划中。但非常重要的是，它们都用于TOP限制实际从索引中拉出的行数。成本只是估计值，值得忽略，但您可以看到这两个计划有很多相似之处，ROW_NUMBER()版本做了少量额外工作来分配数字并进行相应的过滤，两个查询最终只做 2 次读取他们的工作。查询优化器当然认识到过滤ROW_NUMBER()字段的想法，意识到它可以使用 Top 运算符来忽略不需要的行。这两个查询都足够好 -TOP并没有好到值得更改代码，但对于初学者来说它更简单并且可能更清晰。

所以这项工作适用于单一产品。但是我们需要考虑如果我们需要跨多个产品执行此操作会发生什么。

迭代程序员将考虑循环遍历感兴趣的产品并多次调用此查询的想法，我们实际上可以以这种形式编写查询 - 不使用游标，而是使用APPLY. 我正在使用OUTER APPLY，认为如果没有交易，我们可能希望返回带有 NULL 的产品。

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

对此的计划是迭代程序员的方法 - 嵌套循环，对每个产品执行 Top 操作和 Seek（我们之前的 2 次读取）。这对 Product 进行了 4 次读取，对 TransactionHistory 进行了 360 次读取。

使用ROW_NUMBER()，方法是PARTITION BY在OVER子句中使用，这样我们就重新开始为每个产品编号。然后可以像以前一样过滤。该计划最终是完全不同的。TransactionHistory 上的逻辑读取量降低了约 15%，并进行了完整的索引扫描以获取行。

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

但值得注意的是，这个计划有一个昂贵的排序运算符。Merge Join 似乎没有维护 TransactionHistory 中的行顺序，必须借助数据才能找到行号。读取次数较少，但这种阻塞排序可能会让人感到痛苦。使用APPLY，嵌套循环将非常快速地返回第一行，只需几次读取，但使用排序，ROW_NUMBER()只会在大部分工作完成后返回行。

有趣的是，如果ROW_NUMBER()查询使用INNER JOIN而不是LEFT JOIN，则会出现不同的计划。

该计划使用嵌套循环，就像APPLY. 但是没有 Top 运算符，因此它会提取每个产品的所有事务，并且使用比以前更多的读取 - 492 次读取 TransactionHistory。没有充分的理由不在这里选择 Merge Join 选项，所以我猜该计划被认为是“足够好”。仍然 - 它不会阻塞，这很好 - 只是不如APPLY.

PARTITION BY我使用的ROW_NUMBER()列h.ProductID在这两种情况下都是，因为我想在加入 Product 表之前给 QO 生成 RowNum 值的选项。如果我使用p.ProductID，我们会看到与变体相同的形状计划INNER JOIN。

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

但是 Join 运算符说的是“Left Outer Join”而不是“Inner Join”。TransactionHistory 表的读取次数仍略低于 500 次。

无论如何 - 回到手头的问题......

我们已经回答了问题 1，您可以从中选择两个选项。就个人而言，我喜欢这个APPLY选项。

要将其扩展为使用可变数字（问题 2），5只需相应地更改。哦，我添加了另一个索引，这样就有一个Production.Product.Name包含该DaysToManufacture列的索引。

WITH Numbered AS
(
SELECT p.Name, p.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

这两个计划几乎与之前的计划相同！

同样，忽略估计的成本——但我仍然喜欢 TOP 方案，因为它要简单得多，而且该计划没有阻塞运算符。TransactionHistory 上的读取较少，因为 中的零数量很多DaysToManufacture，但在现实生活中，我怀疑我们会选择该列。;)

避免阻塞的一种方法是提出一个计划来处理ROW_NUMBER()连接右侧（在计划中）的位。我们可以通过在 CTE 之外进行连接来说服这种情况发生。

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
)
SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM Production.Product p
LEFT JOIN Numbered t ON t.ProductID = p.ProductID
    AND t.RowNum <= 5 * p.DaysToManufacture
WHERE p.Name >= 'M' AND p.Name < 'S';

这里的方案看起来比较简单——不是阻塞，而是有隐患。

请注意从 Product 表中提取数据的 Compute Scalar。这正在计算5 * p.DaysToManufacture价值。此值不会传递到从 TransactionHistory 表中提取数据的分支中，而是在 Merge Join 中使用。作为一个残差。

因此，Merge Join 正在消耗所有行，不仅是第一行，但是需要很多行，而是所有行，然后进行残差检查。随着交易数量的增加，这很危险。我不喜欢这种情况 - Merge Joins 中的剩余谓词会迅速升级。APPLY/TOP我更喜欢这个场景的另一个原因。

在恰好是一行的特殊情况下，对于问题 3，我们显然可以使用相同的查询，但使用1而不是5。但是我们有一个额外的选择，那就是使用常规聚合。

SELECT ProductID, MAX(TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

像这样的查询将是一个有用的开始，我们可以轻松地对其进行修改以提取 TransactionID 以达到平局的目的（使用随后将被分解的串联），但我们要么查看整个索引，要么我们逐个产品地深入研究，在这种情况下，我们并没有真正比以前的情况有很大的改进。

但我应该指出，我们正在研究一个特定的场景。使用真实数据以及可能不理想的索引策略，里程可能会有很大差异。尽管我们已经看到它APPLY在这里很强大，但在某些情况下它可能会变慢。但它很少阻塞，因为它倾向于使用嵌套循环，许多人（包括我自己）觉得这很有吸引力。

我没有尝试在这里探索并行性，也没有非常努力地研究问题 3，我认为这是一个特殊情况，由于连接和拆分的复杂性，人们很少想要。这里要考虑的主要事情是这两个选项都非常强大。

我更喜欢APPLY. 很明显，它很好地使用了 Top 运算符，并且很少引起阻塞。

在 SQL Server 2005 及更高版本中执行此操作的典型方法是使用 CTE 和窗口函数。对于每个组的前 n 个，您可以简单地使用ROW_NUMBER()一个PARTITION子句，并在外部查询中对其进行过滤。因此，例如，每个客户的前 5 个最近订单可以这样显示：

DECLARE @top INT;
SET @top = 5;

;WITH grp AS 
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER() OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   FROM dbo.Orders
)
SELECT CustomerID, OrderID, OrderDate
  FROM grp
  WHERE rn <= @top
  ORDER BY CustomerID, OrderDate DESC;

你也可以这样做CROSS APPLY：

DECLARE @top INT;
SET @top = 5;

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
CROSS APPLY 
(
    SELECT TOP (@top) OrderID, OrderDate 
    FROM dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

使用 Paul 指定的附加选项，假设 Customers 表有一列指示每个客户要包含多少行：

;WITH grp AS 
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER() OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   FROM dbo.Orders
)
SELECT c.CustomerID, grp.OrderID, grp.OrderDate
  FROM grp 
  INNER JOIN dbo.Customers AS c
  ON grp.CustomerID = c.CustomerID
  AND grp.rn <= c.Number_of_Recent_Orders_to_Show
  ORDER BY c.CustomerID, grp.OrderDate DESC;

再次，使用CROSS APPLY并合并添加的选项，即客户的行数由客户表中的某些列决定：

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
CROSS APPLY 
(
    SELECT TOP (c.Number_of_Recent_Orders_to_Show) OrderID, OrderDate 
    FROM dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

请注意，这些将根据数据分布和支持索引的可用性而有所不同，因此优化性能和获得最佳计划实际上取决于本地因素。

就个人而言，我更喜欢 CTE 和窗口解决方案而不是CROSS APPLY/TOP因为它们更好地分离逻辑并且更直观（对我而言）。一般来说（在这种情况下和我的一般经验中），CTE 方法会产生更有效的计划（下面的示例），但这不应被视为普遍真理 - 您应该始终测试您的场景，特别是如果索引已更改或数据出现明显偏差。

AdventureWorks 示例 - 没有任何更改

1. 列出表中最近的五个交易日期和 ID TransactionHistory，每个产品以字母从 M 到 R（含）开头。

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn <= 5;

-- CROSS APPLY

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
CROSS APPLY
(
  SELECT TOP (5) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC
) AS t
WHERE p.Name >= N'M' AND p.Name < N'S';

这两者在运行时指标上的比较：

CTE/OVER()计划：

CROSS APPLY计划：

CTE 计划看起来更复杂，但实际上效率更高。很少关注估计的成本百分比数字，而是关注更重要的实际观察，例如更少的读取和更短的持续时间。我也在没有并行性的情况下运行这些，这没有区别。运行时指标和 CTE 计划（CROSS APPLY计划保持不变）：

1. 再次相同，但n每个产品都有历史行，其中是Product 属性n的五倍。DaysToManufacture

这里需要非常小的更改。对于 CTE，我们可以在内部查询中添加一列，并在外部查询上进行过滤；对于CROSS APPLY，我们可以在相关性里面进行计算TOP。你会认为这会给CROSS APPLY解决方案带来一些效率，但在这种情况下不会发生这种情况。查询：

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, p.DaysToManufacture, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn <= (5 * DaysToManufacture);

-- CROSS APPLY

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
CROSS APPLY
(
  SELECT TOP (5 * p.DaysToManufacture) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC
) AS t
WHERE p.Name >= N'M' AND p.Name < N'S';

运行时结果：

并行 CTE/OVER()计划：

单线程 CTE/OVER()计划：

CROSS APPLY计划：

1. 同样，对于每个产品都需要一个历史行的特殊情况（最近的单个条目TransactionDate， tie-break on TransactionID.

再次，这里的小改动。在 CTE 解决方案中，我们添加TransactionID到OVER()子句，并将外部过滤器更改为rn = 1. 对于CROSS APPLY，我们将其更改TOP为TOP (1)，并添加TransactionID到内部ORDER BY。

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC, TransactionID DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn = 1;

-- CROSS APPLY

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
CROSS APPLY
(
  SELECT TOP (1) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC, TransactionID DESC
) AS t
WHERE p.Name >= N'M' AND p.Name < N'S';

运行时结果：

并行 CTE/OVER()计划：

单线程 CTE / OVER() 计划：

CROSS APPLY计划：

开窗函数并不总是最好的选择（试一试COUNT(*) OVER()），这并不是解决每组 n 行问题的唯一两种方法，但在这种特定情况下 - 给定架构、现有索引和数据分布 -在所有有意义的账户中，CTE 的表现都更好。

AdventureWorks 示例 - 可以灵活地添加索引

但是，如果您添加一个支持索引，类似于Paul 在评论中提到的索引，但第 2 列和第 3 列是有序的DESC：

CREATE UNIQUE NONCLUSTERED INDEX UQ3 ON Production.TransactionHistory 
  (ProductID, TransactionDate DESC, TransactionID DESC);

实际上，您会得到更有利的计划，并且CROSS APPLY在所有三种情况下，指标都会翻转以支持该方法：

如果这是我的生产环境，我可能会对这种情况下的持续时间感到满意，并且不会费心进一步优化。

这在不支持APPLYorOVER()子句的 SQL Server 2000 中更加丑陋。