TOP (Transact-SQL)TOP (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

将在查询结果集中返回的行数限制到 SQL ServerSQL Server 中的指定行数或行的百分比。Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL ServerSQL Server. 将 TOP 用于 ORDER BY 子句时,结果集被限制为前 N 个已排序的行。When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. 否则,TOP 将以未定义的顺序返回前 N 行。Otherwise, TOP returns the first N number of rows in an undefined order. 使用此子句来指定从 SELECT 语句返回的行数。Use this clause to specify the number of rows returned from a SELECT statement. 或者,使用 TOP 来指定受 INSERT、UPDATE、MERGE 或 DELETE 语句影响的行。Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

以下为 SQL Server 和 Azure SQL 数据库的语法:Following is the syntax for SQL Server and Azure SQL Database:

[   
    TOP (expression) [PERCENT]  
    [ WITH TIES ]  
]  

以下为 Azure SQL 数据仓库和并行数据仓库的语法:Following is syntax for Azure SQL Data Warehouse and Parallel Data Warehouse:

[   
    TOP ( expression )   
    [ WITH TIES ]  
]  

参数Arguments

expressionexpression
指定要返回的行数的数值表达式。The numeric expression that specifies the number of rows to be returned. 如果指定 PERCENT,expression 会隐式转换为 float 值。expression is implicitly converted to a float value if you specify PERCENT. 否则,expression 会转换为 bigintOtherwise, expression is converted to bigint.

PERCENTPERCENT
指示查询只返回结果集中前 expression% 的行 。Indicates that the query returns only the first expression percent of rows from the result set. 小数部分的值向上舍入到下一个整数值。Fractional values are rounded up to the next integer value.

WITH TIESWITH TIES
返回与有限结果集中的最后一个位置相关联的两行或更多行。Returns two or more rows that tie for last place in the limited results set. 必须将此参数用于 ORDER BY 子句。You must use this argument with the ORDER BY clause. WITH TIES 可能会导致返回的行数多于在 expression 中指定的值。WITH TIES might cause more rows to be returned than the value specified in expression. 例如,如果 expression 设置为 5,而 2 个其他行与第 5 行中 ORDER BY 列的值匹配,则结果集将包含 7 行。For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.

仅当还指定了 ORDER BY 子句时,可以使用 WITH TIES 参数仅在 SELECT 语句中指定 TOP 子句。You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. 返回的记录关联顺序是任意的。The returned order of tying records is arbitrary. ORDER BY 不影响此规则。ORDER BY doesn't affect this rule.

最佳实践Best Practices

在 SELECT 语句中,始终将 ORDER BY 子句与 TOP 语句结合使用。In a SELECT statement, always use an ORDER BY clause with the TOP clause. 因为它是以可预知的方式指明哪些行受 TOP 影响的唯一方法。Because, it's the only way to predictably indicate which rows are affected by TOP.

在 ORDER BY 子句中使用 OFFSET 和 FETCH,而不使用 TOP 子句,以实现查询分页解决方案。Use OFFSET and FETCH in the ORDER BY clause instead of the TOP clause to implement a query paging solution. 使用 OFFSET 和 FETCH 子句更容易实现分页解决方案(也即,将数据块或页发送到客户端)。A paging solution (that is, sending chunks or "pages" of data to the client) is easier to implement using OFFSET and FETCH clauses. 有关详细信息,请参阅 ORDER BY 子句 (Transact-SQL)For more information, see ORDER BY Clause (Transact-SQL).

使用 TOP(或 OFFSET 和 FETCH)而非 SET ROWCOUNT 限制返回的行数。Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. 这些方法之所以优于使用 SET ROWCOUNT,原因包括以下各项:These methods are preferred over using SET ROWCOUNT for the following reasons:

  • 作为 SELECT 语句的一部分,查询优化器在查询优化期间可能会考虑 TOP 或 FETCH 子句中 expression 的值 。As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. 由于在运行查询的语句外部使用 SET ROWCOUNT,不会在查询计划中考虑它的值。Because you use SET ROWCOUNT outside of a statement that runs a query, its value can't be considered in a query plan.

兼容性支持Compatibility Support

为了向后兼容,括号在 SELECT 语句中是可选的。For backward compatibility, the parentheses are optional in SELECT statements. 我们建议在 SELECT 语句中始终为 TOP 使用括号。We recommend that you always use parentheses for TOP in SELECT statements. 这样做可与要求在 INSERT、UPDATE、MERGE 和 DELETE 语句中使用它的做法保持一致。Doing so provides consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements.

互操作性Interoperability

TOP 表达式不影响由于触发器而可能运行的语句。The TOP expression doesn't affect statements that might run because of a trigger. 触发器中的 inserteddeleted 表将只返回确实受 INSERT、UPDATE、MERGE 或 DELETE 语句影响的那些行。The inserted and deleted tables in the triggers return only the rows that are truly affected by the INSERT, UPDATE, MERGE, or DELETE statements. 例如,INSERT TRIGGER 因使用 TOP 子句的 INSERT 语句而激发。For example, if an INSERT TRIGGER fires as the result of an INSERT statement that used a TOP clause.

SQL ServerSQL Server 允许通过视图更新行。allows for updating rows through views. 由于可在视图定义中包含 TOP 子句,如果更新后行不再符合 TOP 表达式的要求,则某些行可能会从视图中消失。Because you can include the TOP clause in the view definition, certain rows may disappear from the view if the rows no longer meet the requirements of the TOP expression due to an update.

如果在 MERGE 语句中指定,TOP 子句会在整个源表和整个目标表联接应用。When specified in the MERGE statement, the TOP clause applies after the entire source table and the entire target table are joined. 而且,不符合执行插入、更新或删除操作要求的联接行会被删除。And, the joined rows that don't qualify for an insert, update, or delete action are removed. TOP 子句将联接行的数量进一步减少为指定值,并且以一种无序方式对其余联接行应用插入、更新或删除操作。The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions apply to the remaining joined rows in an unordered way. 也就是说,在 WHEN 子句中定义的操作中,这些行是无序分布的。That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. 例如,如果指定 TOP (10) 会影响这些行中的 10 行,则 7 行可能会更新,3 行可能会插入。For example, if specifying TOP (10) affects 10 rows, of these rows, seven may be updated and three inserted. 或者,1 行可能会删除、5 行可能会更新以及 4 行可能会插入,以此类推。Or, one may be deleted, five updated, and four inserted, and so on. 由于 MERGE 语句对源表和目标表都进行完全表扫描,在使用 TOP 子句通过创建多个批处理来修改大型表时,I/O 性能可能会受到影响。Because the MERGE statement does a full table scan of both the source and target tables, I/O performance can be affected when you use the TOP clause to modify a large table by creating multiple batches. 在这种情况下,请务必要确保所有连续批处理都以新行为目标。In this scenario, it's important to ensure that all successive batches target new rows.

在包含 UNION、UNION ALL、EXCEPT 或 INTERSECT 运算符的查询中指定 TOP 子句时,应特别小心。Use caution when you're specifying the TOP clause in a query that contains a UNION, UNION ALL, EXCEPT, or INTERSECT operator. 此时可以编写一个返回意外结果的查询,因为当在选择操作中使用这些运算符时,以逻辑方式处理 TOP 和 ORDER BY 子句的顺序并不总是直观的。It's possible to write a query that returns unexpected results because the order in which the TOP and ORDER BY clauses are logically processed isn't always intuitive when these operators are used in a select operation. 例如,给定以下表和数据,假定您要返回最便宜的红色汽车和最便宜的蓝色汽车。For example, given the following table and data, assume that you want to return the least expensive red car and the least expensive blue car. 也就是红色的小轿车和蓝色的货车。That is, the red sedan and the blue van.

CREATE TABLE dbo.Cars(Model varchar(15), Price money, Color varchar(10));  
INSERT dbo.Cars VALUES  
    ('sedan', 10000, 'red'), ('convertible', 15000, 'blue'),   
    ('coupe', 20000, 'red'), ('van', 8000, 'blue');  

为了实现这些结果,您可能会编写以下查询。To achieve these results, you might write the following query.

SELECT TOP(1) Model, Color, Price  
FROM dbo.Cars  
WHERE Color = 'red'  
UNION ALL  
SELECT TOP(1) Model, Color, Price  
FROM dbo.Cars  
WHERE Color = 'blue'  
ORDER BY Price ASC;  
GO    

结果集如下。Following is the result set.

Model         Color      Price  
------------- ---------- -------  
sedan         red        10000.00  
convertible   blue       15000.00

此时返回意外结果,因为从逻辑上讲,会先运行 TOP 子句,然后运行 ORDER BY 子句,这会对运算符(在这种情况下为 UNION ALL)的结果进行排序。The unexpected results are returned because the TOP clause logically runs before the ORDER BY clause, which sorts the results of the operator (UNION ALL in this case). 因此,前一个查询返回任何一辆红色汽车和任何一辆蓝色汽车,然后按价格对该联合的结果排序。So, the previous query returns any one red car and any one blue car and then orders the result of that union by the price. 下面的示例显示了编写此查询以获得所需结果的正确方法。The following example shows the correct method of writing this query to achieve the desired result.

SELECT Model, Color, Price  
FROM (SELECT TOP(1) Model, Color, Price  
      FROM dbo.Cars  
      WHERE Color = 'red'  
      ORDER BY Price ASC) AS a  
UNION ALL  
SELECT Model, Color, Price  
FROM (SELECT TOP(1) Model, Color, Price  
      FROM dbo.Cars  
      WHERE Color = 'blue'  
      ORDER BY Price ASC) AS b;  
GO    

通过在嵌套 select 操作中使用 TOP 和 ORDER BY,可确保将 ORDER BY 子句的结果应用于 TOP 子句,而不对 UNION 运算的结果排序。By using TOP and ORDER BY in a subselect operation, you ensure that the results of the ORDER BY clause are applied to the TOP clause and not to sorting the result of the UNION operation.

下面是结果集:Here is the result set.

Model         Color      Price  
------------- ---------- -------  
sedan         red        10000.00  
van           blue        8000.00

限制和局限Limitations and Restrictions

如果将 TOP 用于 INSERT、UPDATE、MERGE 或 DELETE,引用的行不按任何顺序排列。When you use TOP with INSERT, UPDATE, MERGE, or DELETE, the referenced rows aren't arranged in any order. 而且,不能直接在这些语句中指定 ORDER BY 子句。And, you can't directly specify the ORDER BY clause in these statements. 如果需要使用 TOP 来插入、删除或修改按有意义的时间顺序排列的行,请将 TOP 用于在嵌套 select 语句中指定的 ORDER BY 子句。If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, use TOP with an ORDER BY clause specified in a subselect statement. 请参阅本文的以下“示例”部分。See the following Examples section in this article.

在已分区视图中,不能在 UPDATE 和 DELETE 语句中使用 TOP。You can't use TOP in an UPDATE and DELETE statements on partitioned views.

TOP 不能与 OFFSET 和 FETCH 在同一个查询表达式(同一个查询范围)中结合使用。You can't combine TOP with OFFSET and FETCH in the same query expression (in the same query scope). 有关详细信息,请参阅 ORDER BY 子句 (Transact-SQL)For more information, see ORDER BY Clause (Transact-SQL).

示例Examples

类别Category 作为特征的语法元素Featured syntax elements
基本语法Basic syntax TOP • PERCENTTOP • PERCENT
包括关联值Including tie values WITH TIESWITH TIES
限制受 DELETE、INSERT 或 UPDATE 影响的行Limiting the rows affected by DELETE, INSERT, or UPDATE DELETE • INSERT • UPDATEDELETE • INSERT • UPDATE

基本语法Basic syntax

本节中的示例说明了使用最低要求的语法的 ORDER BY 子句的基本功能。Examples in this section demonstrate the basic functionality of the ORDER BY clause using the minimum required syntax.

A.A. 使用 TOP 以及一个常量值Using TOP with a constant value

下面的示例使用常量值以指定在查询结果集中返回的员工数。The following examples use a constant value to specify the number of employees that are returned in the query result set. 在第一个示例中,返回前 10 个未定义的行,因为此时没有使用 ORDER BY 子句。In the first example, the first 10 undefined rows are returned because an ORDER BY clause isn't used. 在第二个示例中,使用了 ORDER BY 子句来返回前 10 个最近雇用的员工。In the second example, an ORDER BY clause is used to return the top 10 recently hired employees.

USE AdventureWorks2012;  
GO  
-- Select the first 10 random employees.  
SELECT TOP(10)JobTitle, HireDate  
FROM HumanResources.Employee;  
GO  
-- Select the first 10 employees hired most recently.  
SELECT TOP(10)JobTitle, HireDate  
FROM HumanResources.Employee  
ORDER BY HireDate DESC;  
GO  

B.B. 使用 TOP 以及一个变量Using TOP with a variable

下面的示例使用变量以指定在查询结果集中返回的员工数。The following example uses a variable to specify the number of employees that are returned in the query result set.

USE AdventureWorks2012;  
GO  
DECLARE @p AS int = 10;  
SELECT TOP(@p)JobTitle, HireDate, VacationHours  
FROM HumanResources.Employee  
ORDER BY VacationHours DESC;  
GO  

C.C. 指定百分比Specifying a percentage

下面的示例使用 PERCENT 以指定在查询结果集中返回的员工数。The following example uses PERCENT to specify the number of employees that are returned in the query result set. HumanResources.Employee 表中有 290 名员工。There are 290 employees in the HumanResources.Employee table. 因为 290 的 5% 是一个小数值,该值会向上舍入为下一个整数。Because five percent of 290 is a fractional value, the value is rounded up to the next whole number.

USE AdventureWorks2012;  
GO  
SELECT TOP(5)PERCENT JobTitle, HireDate  
FROM HumanResources.Employee  
ORDER BY HireDate DESC;  
GO    

包括关联值Including tie values

A.A. 使用 WITH TIES 以包含与最后一行中的值匹配的行Using WITH TIES to include rows that match the values in the last row

以下示例获取所有雇员中薪金最高的 10 个百分比的雇员,并根据其薪金按降序返回。The following example gets the top 10 percent of all employees with the highest salary and returns them in descending order according to their salary. 指定 WITH TIES 可确保结果集中同时包含其薪金与返回的最低薪金(最后一行)相同的所有雇员,即使这样做会超过雇员总数的 10 个百分比。Specifying WITH TIES ensures that employees with salaries equal to the lowest salary returned (the last row) are also included in the result set, even if it exceeds 10 percent of employees.

USE AdventureWorks2012;  
GO  
SELECT TOP(10) PERCENT WITH TIES  
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate  
FROM Person.Person AS pp   
    INNER JOIN HumanResources.Employee AS e  
        ON pp.BusinessEntityID = e.BusinessEntityID  
    INNER JOIN HumanResources.EmployeePayHistory AS r  
        ON r.BusinessEntityID = e.BusinessEntityID  
ORDER BY Rate DESC;  
GO    

限制受 DELETE、INSERT 或 UPDATE 影响的行Limiting the rows affected by DELETE, INSERT, or UPDATE

A.A. 使用 TOP 限制删除的行数Using TOP to limit the number of rows deleted

如果将 TOP (n) 子句用于 DELETE,将针对未定义的选定 n 行执行删除操作。When you use a TOP (n) clause with DELETE, the delete operation is done on an undefined selection of n number of rows. 也即,DELETE 语句选择满足 WHERE 子句中定义的条件的任何数目 (n) 的行 。That is, the DELETE statement chooses any (n) number of rows that meet the criteria defined in the WHERE clause. 下面的示例从 20 表中删除其到期日期早于 2002 年 7 月 1 日的 PurchaseOrderDetail 行。The following example deletes 20 rows from the PurchaseOrderDetail table that have due dates earlier than July 1, 2002.

USE AdventureWorks2012;  
GO  
DELETE TOP (20)   
FROM Purchasing.PurchaseOrderDetail  
WHERE DueDate < '20020701';  
GO  

如果想要使用 TOP 来删除按有意义的时间顺序排列的行,请在嵌套 select 语句中将 TOP 用于 ORDER BY。If you want to use TOP to delete rows in a meaningful chronological order, use TOP with ORDER BY in a subselect statement. 下面的查询从 PurchaseOrderDetail 表中删除了其到期日期最早的 10 行。The following query deletes the 10 rows of the PurchaseOrderDetail table that have the earliest due dates. 为了确保仅删除 10 行,嵌套 Select 语句 (PurchaseOrderID) 中指定的列将成为表的主键。To ensure that only 10 rows are deleted, the column specified in the subselect statement (PurchaseOrderID) is the primary key of the table. 如果指定列包含重复的值,则在嵌套 Select 语句中使用非键列可能会导致删除的行超过 10 个。Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.

USE AdventureWorks2012;  
GO  
DELETE FROM Purchasing.PurchaseOrderDetail  
WHERE PurchaseOrderDetailID IN  
   (SELECT TOP 10 PurchaseOrderDetailID   
    FROM Purchasing.PurchaseOrderDetail   
    ORDER BY DueDate ASC);  
GO  

B.B. 使用 TOP 限制插入的行数Using TOP to limit the number of rows inserted

以下示例创建 EmployeeSales 表,并插入 HumanResources.Employee 表中的前 5 名雇员的姓名和本年度到目前为止的销售数据。The following example creates the table EmployeeSales and inserts the name and year-to-date sales data for the top five employees from the table HumanResources.Employee. INSERT 语句选择由满足 WHERE 子句中定义的条件的 SELECT 语句返回的任意 5 行。The INSERT statement chooses any five rows returned by the SELECT statement that meet the criteria defined in the WHERE clause. OUTPUT 子句将显示插入 EmployeeSales 表中的行。The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. 请注意,SELECT 语句中的 ORDER BY 子句不用于确定前 5 名雇员。Notice that the ORDER BY clause in the SELECT statement isn't used to determine the top five employees.

USE AdventureWorks2012 ;  
GO  
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL  
    DROP TABLE dbo.EmployeeSales;  
GO  
CREATE TABLE dbo.EmployeeSales  
( EmployeeID   nvarchar(11) NOT NULL,  
  LastName     nvarchar(20) NOT NULL,  
  FirstName    nvarchar(20) NOT NULL,  
  YearlySales  money NOT NULL  
 );  
GO  
INSERT TOP(5)INTO dbo.EmployeeSales  
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales  
    SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.SalesYTD > 250000.00  
    ORDER BY sp.SalesYTD DESC;  
GO    

如果想要使用 TOP 来插入按有意义的时间顺序排列的行,请在嵌套 select 语句中将 TOP 用于 ORDER BY。If you want to use TOP to insert rows in a meaningful chronological order, use TOP with ORDER BY in a subselect statement. 下面的示例演示如何执行此操作。The following example show how to do this. OUTPUT 子句将显示插入 EmployeeSales 表中的行。The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. 请注意,现在基于 ORDER BY 子句的结果(而非未定义的行)插入前 5 名雇员。Notice that the top five employees are now inserted based on the results of the ORDER BY clause instead of undefined rows.

INSERT INTO dbo.EmployeeSales  
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales  
    SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.SalesYTD > 250000.00  
    ORDER BY sp.SalesYTD DESC;  
GO    

C.C. 使用 TOP 限制更新的行数Using TOP to limit the number of rows updated

以下示例使用 TOP 子句更新表中的行。The following example uses the TOP clause to update rows in a table. 如果将 TOP (n) 子句用于 UPDATE,将针对未定义数量的行运行更新操作。When you use a TOP (n) clause with UPDATE, the update operation runs on an undefined number of rows. 也即,UPDATE 语句选择满足 WHERE 子句中定义的条件的任何数目 (n) 的行 。That is, the UPDATE statement chooses any (n) number of rows that meet the criteria defined in the WHERE clause. 下列示例将 10 个客户从一位销售人员分配给了另一位。The following example assigns 10 customers from one salesperson to another.

USE AdventureWorks2012;  
UPDATE TOP (10) Sales.Store  
SET SalesPersonID = 276  
WHERE SalesPersonID = 275;  
GO  

如果需要使用 TOP 来应用按有意义的时间顺序排列的更新,您必须同时使用 TOP 和 ORDER BY 子句。If you have to use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. 下列示例更新了雇佣最早的 10 名雇员的假期小时数。The following example updates the vacation hours of the 10 employees with the earliest hire dates.

UPDATE HumanResources.Employee  
SET VacationHours = VacationHours + 8  
FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee  
     ORDER BY HireDate ASC) AS th  
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;  
GO  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

下列示例将返回匹配查询条件的前 31 行。The following example returns the top 31 rows that match the query criteria. ORDER BY 子句可确保所返回的 31 行是按字母顺序排序的 LastName 列的前 31 行。The ORDER BY clause ensures that the 31 returned rows are the first 31 rows based on an alphabetical ordering of the LastName column.

使用 TOP,且不指定关联 。Using TOP without specifying ties.

SELECT TOP (31) FirstName, LastName   
FROM DimEmployee ORDER BY LastName;  

结果:返回 31 行。Result: 31 rows are returned.

使用 TOP,并指定 WITH TIES。Using TOP, specifying WITH TIES.

SELECT TOP (31) WITH TIES FirstName, LastName   
FROM DimEmployee ORDER BY LastName;  

结果:返回 33 行,因为有 3 名名为 Brown 的员工与第 31 行相关联。Result: 33 rows are returned, because three employees named Brown tie for the 31st row.

另请参阅See Also

SELECT (Transact-SQL) SELECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
ORDER BY 子句 (Transact-SQL) ORDER BY Clause (Transact-SQL)
SET ROWCOUNT (Transact-SQL) SET ROWCOUNT (Transact-SQL)
MERGE (Transact-SQL)MERGE (Transact-SQL)