SQL教程：SELECT语句中ORDER BY，用TOP限制行

SQL Prompt是一款实用的SQL语法提示工具。SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读–当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt正式版

使用SQL提示查找代码气味：SELECT语句中没有ORDER BY的TOP（BP006）

在SELECT语句中使用TOP，而没有后续的ORDER BY子句，在SQL Server中是合法的，但没有意义，因为询问TOP x行意味着保证数据按一定顺序存在，并且表没有隐式逻辑顺序。您必须指定顺序。

在SELECT语句中，应始终将一个ORDER BY子句与该TOP子句一起使用，以指定哪些行受过TOP滤器影响。如果您需要实现一个应用程序窗口小部件寻呼解决方案，送块或数据到客户端的“页面”，所以用户可以通过滚动数据，它是更好，更容易使用OFFSET- FETCH节中ORDER BY节，而不是TOP条款。

SQL Prompt（BP006）中的“最佳实践”代码分析规则中包含一个避免TOP在SELECT语句中使用而不带的建议。

用TOP限制行

TOP不是标准的SQL，但是很直观。如果您只想从表源中获取一些示例行，那么很容易使用TOP不带ORDER BY子句的关键字。单个表可能符合聚集索引的顺序，但是由于并行性，即使这样也不能保证。

如果我们超越了查询单个表并进行一些联接，那么“自然”顺序就不太明显了。也许您在AdventureWorks中，只需要五个客户，任何五个客户及其地址。这样做是完全合法的，但是如果您随后忘记执行此操作的原因，则有些危险。
SELECT TOP 5 Person.Title, Person.FirstName, Person.MiddleName,
Person.LastName, Address.AddressLine1, Address.AddressLine2, Address.City,
Address.PostalCode, AddressType.Name
FROM Sales.Customer
INNER JOIN Person.Person
ON Customer.PersonID = Person.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress
ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
INNER JOIN Person.Address
ON BusinessEntityAddress.AddressID = Address.AddressID
INNER JOIN Person.AddressType
ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;
清单1
您将获得期望的结果，只是查询返回的前五个客户。我得到的顺序是Sales.Customer表的聚集索引的顺序PK_Customer_CustomerID，从最低customer_id的人（而不是商店）开始。不同的执行策略可能会改变这一点。您不能保证确定的结果。如果您只想在开发过程中获取样本，这可能会很好，但是在生产系统中，您真正想要的是排名前五位的客户，并根据其排名顺序确定地址，这些排名由某些属性（例如花费多少）决定。你真的需要那个ORDER BY。

简而言之，SQL表不能保证其固有顺序的一致性。您可能已经设置了a PRIMARY KEY，使您的表值具有一些基本的顺序，但这不能保证始终如一。SQL Server保留在创建执行计划以返回结果的过程中引入所需的任何优化的权利，即使这意味着按不同的顺序传递结果。简而言之，除非您通过ORDER BY声明将其明确显示，否则您不能保证结果将按预期的顺序返回。

因此，我们回到了完全合理的要求，即开发人员必须能够查询查询中代表性的行样本。应该怎么做？

SET ROWCOUNT和TABLESAMPLE：它们有帮助吗？

曾经有一段时间，我们不得不使用该SET ROWCOUNT语句来限制返回的行数。这样做的一个缺点是查询优化器无法根据请求的行数来创建有效的计划，因为这ROWCOUNT是会话或过程/触发器范围的设置，对于查询中的查询优化器而言不可见。

同样，有可能忘记您已设置了ROWCOUNT并且忽略了“取消设置”它。另一个缺点是您无法将值传递给变量。TOP更好，因为它在语句级别起作用，并且您可以将行值或百分比作为变量或表达式传递。

您可能认为您可以使用该TABLESAMPLE子句可靠地从表中获取有限数量的行。唯一的问题是它不能像广告中那样工作，即使它确实像广告中那样工作，也只能在表上工作，而不是各种各样的表源。
SELECT * FROM Sales.Customer TABLESAMPLE SYSTEM (5);
清单2
这应该将从FROM子句中的表返回的行数限制为样本数或PERCENT行数。快速测试将向您展示为什么没人使用它。
DROP TABLE IF EXISTS #Result;
CREATE TABLE #Result (TheOrder INT IDENTITY, TheRowsReturned INT);
GO
INSERT INTO #Result (TheRowsReturned)
SELECT Count(*) FROM Sales.Customer TABLESAMPLE(200 ROWS);
GO 30
SELECT #Result.TheOrder, #Result.TheRowsReturned FROM #Result;

清单3
使用TOP…ORDER BY获取有意义的表样本

到目前为止，由于TABLESAMPLE已损坏，因此从表中获取样品的做法有些尴尬。
SELECT TOP 5 * FROM Sales.Customer
清单4
为什么这很尴尬？清单4将给您返回五行，但是您不能完全依赖返回的行，尽管它可能按照的顺序排列PRIMARY KEY，因为我们只访问一个表。但是，sales.customer它有点“技巧”表，因为它使用了多态关联，并且19820行中的前700个代表商店，而不是人。因此，清单4可能会给出该表的一个非常不具有代表性的示例，因为您可能会轻易地对表中的数据产生不正确的印象，以为客户是商店，而大多数是人！

大多数开发人员希望看到的是他们正在调查的表的几行，是随机抽取的，但是如果您希望以随机顺序抽取示例，则必须明确说明。
SELECT TOP 5 * FROM Sales.Customer ORDER BY NewId()
清单5
这将以随机顺序返回五行，但需要更多资源才能返回结果。如果您不是在处理“技巧”表，而是只在乎顺序，而无需在代码中指出这一事实，则SQL Server将接受任何系统函数，例如@@version或host_name()，甚至ORDER BY（SELECT NULL）。在SQL Server拒绝需要Windows的窗口函数中的代码的情况下，通常会看到这种技巧ORDER BY。它的意思是“我知道，我知道，但我故意这样做 ”。
SELECT TOP 10 * FROM Sales.Customer ORDER BY @@identity
清单6
如果你很高兴与您通过使用获得的记录TOP没有ORDER BY，那么最好是完全明确的，并指出，你真的希望它由PRIMARY KEY场
SELECT TOP 5 *
FROM Sales.Customer
ORDER BY Customer.CustomerID;
清单7
将TOP与ORDER BY结合使用以报告查询
TOP出于报告目的而独立存在。经理们喜欢顶级客户和顶级销售员的名单。在这一点上，该ORDER BY部分变得至关重要。
SELECT TOP 10 Person.BusinessEntityID, Sum(SalesOrderHeader.TotalDue) AS expenditure
FROM Sales.SalesPerson
INNER JOIN Sales.SalesOrderHeader
ON SalesPerson.BusinessEntityID = SalesOrderHeader.SalesPersonID
INNER JOIN Person.Person
ON SalesPerson.BusinessEntityID = Person.BusinessEntityID
GROUP BY Person.BusinessEntityID
ORDER BY Sum(SalesOrderHeader.TotalDue) DESC;
清单8
这为您提供了表现最佳的十位销售人员。

我们可能会认为该报告并没有真正告诉我们销售员是谁，因此我们对其进行了调整。
SELECT SalesPerformance.SalesValue,
Coalesce(Person.Title + ’ ', ‘’) + Person.FirstName

• Coalesce(’ ’ + Person.MiddleName, ‘’) + ’ ’ + Person.LastName
• Coalesce(’ ’ + Person.Suffix, ‘’) AS SalesPerson
  FROM
  (
  SELECT TOP 10 SalesPerson.BusinessEntityID AS salesPerson,
  Sum(SalesOrderHeader.TotalDue) AS SalesValue
  FROM Sales.SalesPerson
  INNER JOIN Sales.SalesOrderHeader
  ON SalesPerson.BusinessEntityID = SalesOrderHeader.SalesPersonID
  INNER JOIN Person.Person
  ON SalesPerson.BusinessEntityID = Person.BusinessEntityID
  GROUP BY SalesPerson.BusinessEntityID
  ORDER BY Sum(SalesOrderHeader.TotalDue) DESC
  ) AS SalesPerformance(SalesPerson, SalesValue)
  INNER JOIN Person.Person
  ON SalesPerformance.SalesPerson = Person.BusinessEntityID
  ORDER BY SalesPerformance.SalesValue DESC
  清单9
  为什么我们需要第二次ORDER BY呢？原始SQL是一个汇总查询，我们需要前10个汇总销售总额，因此我们必须在其上强加一个订单。这没有固定顺序传递到添加了人员姓名的外部查询。为了确定外部查询的顺序，它也将需要一个显式ORDER BY子句。有时将其称为“演示ORDER BY”或“演示排序”。

使用FETCH-OFFSET而不是TOP
当然，更好的方法是ORDER BY将SQL Server 2012及更高版本中的可选OFFSET– FETCH子句与一起使用TOP。它具有更多的用途，并且也是标准的ANSI I SQL。这是AdventureWorks服务时间最长的20名员工。
SELECT Employee.JobTitle, Employee.HireDate,
Coalesce(Person.Title + ’ ', ‘’) + Person.FirstName

• Coalesce(’ ’ + Person.MiddleName, ‘’) + ’ ’ + Person.LastName
• Coalesce(’ ’ + Person.Suffix, ‘’) AS Name
  FROM HumanResources.Employee
  INNER JOIN Person.Person
  ON Person.BusinessEntityID = Employee.BusinessEntityID
  ORDER BY Employee.HireDate ASC
  OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY;

清单10
现在，借助ORDER BY…OFFSET…ROWS FETCH FIRST…ROWS ONLY，您可以提供在整个名人堂中滚动或翻页的方法。

将TOP与INSERT，UPDATE，MERGE或DELETE一起使用

不鼓励您不使用TOPwith和ORDER BY，这在某些情况下被积极禁止使用，这似乎很奇怪。还有SELECT说法，DELETE，INSERT，MERGE和UPDATE语句都有一个TOP条款。与相比SELECT，您不能有关联ORDER BY子句。让我们来看这个例子。
DROP TABLE IF EXISTS #tempCustomer; --in case it exists
SELECT Customer.CustomerID, Customer.PersonID, Customer.StoreID,
Customer.TerritoryID, Customer.AccountNumber, Customer.rowguid,
Customer.ModifiedDate
INTO #tempCustomer
FROM Sales.Customer --just for the test

UPDATE TOP (10) #tempCustomer
SET #tempCustomer.AccountNumber =
Replace(#tempCustomer.AccountNumber, ‘AW’, ‘PF’)
OUTPUT Deleted.CustomerID, Deleted.AccountNumber AS before,
Inserted.AccountNumber AS after

清单11
现在尝试添加一个ORDER BY子句！它不会允许的。如文档所述：

“在引用的行TOP表达使用INSERT，UPDATE，MERGE或DELETE不设置在任何顺序”。

您必须执行类似的操作。
UPDATE #tempCustomer
SET #tempCustomer.AccountNumber = –
Replace(#tempCustomer.AccountNumber, ‘AW’, ‘PF’)
OUTPUT Deleted.CustomerID, Deleted.AccountNumber AS before,
Inserted.AccountNumber AS AFTER
FROM
(
SELECT TOP 10 CustomerID
FROM #tempCustomer
ORDER BY #tempCustomer.CustomerID DESC
) AS ordered
WHERE #tempCustomer.CustomerID = ordered.CustomerID
GO
清单12
同样，INSERT声明。我们不能使用TOP按有意义的时间顺序插入行。正如书中所说：

“ TOP与一起使用时INSERT，引用的行未按任何顺序排列，并且该ORDER BY子句无法在此语句中直接指定。”

如果需要这样做，则必须TOP与ORDER BY在sub-select语句中指定的子句一起使用。

DELETE有一个TOP子句，但我们也不能使用它。如果您想清除旧的采购订单明细怎么办？您需要确定首先清除最旧的。我们不能ORDER BY在delete语句中放入，但是我们不必这样做。

让我们设置测试。
DROP TABLE IF EXISTS #tempPurchaseOrderDetail; --in case it exists
SELECT POD.PurchaseOrderID, POD.PurchaseOrderDetailID, POD.DueDate,
POD.OrderQty, POD.ProductID, POD.UnitPrice, POD.LineTotal, POD.ReceivedQty,
POD.RejectedQty, POD.StockedQty, POD.ModifiedDate
INTO #tempPurchaseOrderDetail
FROM Purchasing.PurchaseOrderDetail AS POD
清单13
现在，我们删除十个最旧的采购订单明细。
DELETE FROM #tempPurchaseOrderDetail
OUTPUT Deleted.DueDate, Deleted.LineTotal, Deleted.PurchaseOrderID
WHERE PurchaseOrderDetailID IN
(
SELECT TOP 10 PurchaseOrderDetailID
FROM #tempPurchaseOrderDetail
ORDER BY DueDate ASC
);
GO
清单14
那么，TOP如果不能使用，或DELETE，则拥有该过滤器有什么意义呢？好吧，实际上，它可以用在不需要最终以特定顺序删除特定记录集的情况下。INSERTMERGEUPDATE

如果您需要例如定期从生产系统中删除许多行，则使用TOP不带过滤器会ORDER BY节省生命。删除将被记录，并且还可能导致锁升级。我曾经不得不设计一个系统，该系统定期从SQL Server数据库中清除一百万行。最佳方法是在吃大象时要连续咬很多东西，而不是一口吃下去。

我们可以很容易地说明这一点，尽管在拥有一个工作系统之前，您将看不到它的优势，尤其是在删除，更新，插入或合并时需要访问该表的系统。同样，我们将使用临时表来说明这一点，以免干扰AdventureWorks的正常运行。
DROP TABLE IF EXISTS #tempPurchaseOrderDetail; --in case it exists
SELECT POD.PurchaseOrderID, POD.PurchaseOrderDetailID, POD.DueDate,
POD.OrderQty, POD.ProductID, POD.UnitPrice, POD.LineTotal, POD.ReceivedQty,
POD.RejectedQty, POD.StockedQty, POD.ModifiedDate
INTO #tempPurchaseOrderDetail
FROM Purchasing.PurchaseOrderDetail AS POD

–we delete rows successively
DECLARE @rowcount INT = 1
WHILE @rowcount > 0
BEGIN
DELETE TOP (200) FROM #tempPurchaseOrderDetail
WHERE #tempPurchaseOrderDetail.DueDate < DateAdd(YEAR, -2, GetDate())
SELECT @rowcount = @@RowCount
END
清单15
过去，我发现像这样的大规模操作通常会受益于分块执行，而分块的大小是与操作系统进行微调以使其正确的问题。对于这样的工作，在TOP没有条款ORDER BY中DELETE，INSERT或UPDATE可以做大规模的变化，在短时间内一步非常有价值的，在硬盘工作的事务处理系统

摘要

TOP语句中的SQL Server 子句SELECT非常有用且直观，但是它允许您省去相关联的ORDER BY子句，以阐明您的想法：TOP从哪个方面看？毕竟，您的TOP十首歌不是最响亮的十首歌，也不是声音最高的十首歌。就唱片销量而言，它们是十大最受欢迎的唱片。您可能会偶然地在开发工作中获得正确的结果，但是在生产中，工作负载，服务器和数据大小可能会导致查询以非常不同的方式进行优化，从而产生不同的结果。

对于处理这类事情的更为通用的方式，我建议使用ORDER BY… OFFSET… FETCH在SQL Server 2012中引入的语法，因为它是更为灵活和符合性。记住，也比TOP过滤器难得多。

试用下载>>>