SELECT - ORDER BY 子句 (Transact-SQL)SELECT - ORDER BY Clause (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 中的查询返回的数据进行排序。Sorts data returned by a query in SQL ServerSQL Server. 可以使用此子句执行以下操作:Use this clause to:

  • 按指定的列列表对查询的结果集进行排序,并有选择地将返回的行限制为指定范围。Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. 除非指定 ORDER BY 子句,否则,不能保证在结果集中返回的行的顺序。The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

  • 确定将排名函数值应用于结果集的顺序。Determine the order in which ranking function values are applied to the result set.

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

备注

Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data Warehouse 中的 SELECT/INTO 或 CREATE TABLE AS SELECT (CTAS) 语句中不支持 ORDER BY。ORDER BY is not supported in SELECT/INTO or CREATE TABLE AS SELECT (CTAS) statements in Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) or 并行数据仓库Parallel Data Warehouse.

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]   
[ <offset_fetch> ]  
  
<offset_fetch> ::=  
{   
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }  
    [  
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY  
    ]  
}  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
[ ORDER BY   
    {  
    order_by_expression   
    [ ASC | DESC ]   
    } [ ,...n ]   
]   

参数Arguments

order_by_expression order_by_expression
指定用于对查询结果集进行排序的列或表达式。Specifies a column or expression on which to sort the query result set. 可以将排序列指定为一个名称或列别名,也可以指定一个表示列在选择列表中所处位置的非负整数。A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the column in the select list.

可以指定多个排序列。Multiple sort columns can be specified. 别名必须是唯一的。Column names must be unique. ORDER BY 子句中的排序列的顺序定义了排序结果集的结构。The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. 也就是说,按第一列对结果集进行排序,然后按第二列对排序列表进行排序,依此类推。That is, the result set is sorted by the first column and then that ordered list is sorted by the second column, and so on.

ORDER BY 子句中引用的列名必须明确对应于选择列表中的列或列别名,或对应于 FROM 子句中指定的表中定义的列。The column names referenced in the ORDER BY clause must correspond to either a column or column alias in the select list or to a column defined in a table specified in the FROM clause without any ambiguities. 如果 ORDER BY 子句引用选择列表中的列别名,则必须单独使用列别名,而不是作为 ORDER BY 子句中的某些表达式的一部分,例如:If the ORDER BY clause references a column alias from the select list, the column alias must be used standalone, and not as a part of some expression in ORDER BY clause, for example:

SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects 
ORDER BY SchemaName; -- correct 
SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects 
ORDER BY SchemaName + ''; -- wrong

COLLATE collation_name COLLATE collation_name
指定应根据 collation_name 中指定的排序规则执行 ORDER BY 操作,而不是根据表或视图中所定义的列的排序规则 。Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name, and not according to the collation of the column as defined in the table or view. collation_name 既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称 。collation_name can be either a Windows collation name or a SQL collation name. 有关详细信息,请参阅 Collation and Unicode SupportFor more information, see Collation and Unicode Support. COLLATE 仅适用于类型为 char、varchar、nchar 和 nvarchar 的列 。COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESCASC | DESC
指定按升序或降序排列指定列中的值。Specifies that the values in the specified column should be sorted in ascending or descending order. ASC 按从最低值到最高值的顺序进行排序。ASC sorts from the lowest value to highest value. DESC 按从最高值到最低值的顺序进行排序。DESC sorts from highest value to lowest value. ASC 是默认排序顺序。ASC is the default sort order. Null 值被视为最低的可能值。Null values are treated as the lowest possible values.

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
指定开始从查询表达式返回行之前跳过的行数。Specifies the number of rows to skip before it starts to return rows from the query expression. 该值可以是大于或等于零的整数常量或表达式。The value can be an integer constant or expression that is greater than or equal to zero.

适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL ServerSQL ServerAzure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database.s

offset_row_count_expression 可以是变量、参数或常量标量子查询 。offset_row_count_expression can be a variable, parameter, or constant scalar subquery. 在使用子查询时,它无法引用在外部查询范围中定义的任何列。When a subquery is used, it cannot reference any columns defined in the outer query scope. 也就是说,它无法与外部查询相关联。That is, it cannot be correlated with the outer query.

ROW 和 ROWS 是同义词,是为了与 ANSI 兼容而提供的。ROW and ROWS are synonyms and are provided for ANSI compatibility.

在查询执行计划中,将在 TOP 查询运算符的 Offset 属性中显示偏移行数值 。In query execution plans, the offset row count value is displayed in the Offset attribute of the TOP query operator.

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
指定在处理 OFFSET 子句后返回的行数。Specifies the number of rows to return after the OFFSET clause has been processed. 该值可以是大于或等于 1 的整数常量或表达式。The value can be an integer constant or expression that is greater than or equal to one.

适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL ServerSQL ServerAzure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database.

fetch_row_count_expression 可以是变量、参数或常量标量子查询 。fetch_row_count_expression can be a variable, parameter, or constant scalar subquery. 在使用子查询时,它无法引用在外部查询范围中定义的任何列。When a subquery is used, it cannot reference any columns defined in the outer query scope. 也就是说,它无法与外部查询相关联。That is, it cannot be correlated with the outer query.

FIRST 和 NEXT 是同义词,是为了与 ANSI 兼容而提供的。FIRST and NEXT are synonyms and are provided for ANSI compatibility.

ROW 和 ROWS 是同义词,是为了与 ANSI 兼容而提供的。ROW and ROWS are synonyms and are provided for ANSI compatibility.

在查询执行计划中,将在 TOP 查询运算符的 Rows 或 Top 属性中显示偏移行数值 。In query execution plans, the offset row count value is displayed in the Rows or Top attribute of the TOP query operator.

最佳实践Best Practices

避免将 ORDER BY 子句中的整数指定为选择列表中的列位置表示形式Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. 例如,虽然 SELECT ProductID, Name FROM Production.Production ORDER BY 2 等语句是有效的,但与指定实际列名相比,其他人并不容易理解该语句。For example, although a statement such as SELECT ProductID, Name FROM Production.Production ORDER BY 2 is valid, the statement is not as easily understood by others compared with specifying the actual column name. 此外,对选择列表的更改(如更改列顺序或添加新列)需要修改 ORDER BY 子句,以避免出现意外结果。In addition, changes to the select list, such as changing the column order or adding new columns, requires modifying the ORDER BY clause in order to avoid unexpected results.

在 SELECT TOP (N) 语句中,请始终使用 ORDER BY 子句 。In a SELECT TOP (N) statement, always use an ORDER BY clause. 这是以可预知的方式指明哪些行受 TOP 影响的唯一方法。This is the only way to predictably indicate which rows are affected by TOP. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

互操作性Interoperability

在与 SELECT…INTO 语句一起使用以从另一来源插入行时,ORDER BY 子句不能保证按指定的顺序插入这些行。When used with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.

在视图中使用 OFFSET 和 FETCH 并不会更改该视图的 Updateability 属性。Using OFFSET and FETCH in a view does not change the updateability property of the view.

限制和局限Limitations and Restrictions

ORDER BY 子句中的列数没有限制;但是,在 ORDER BY 子句中指定的列的总大小不能超过 8,060 个字节。There is no limit to the number of columns in the ORDER BY clause; however, the total size of the columns specified in an ORDER BY clause cannot exceed 8,060 bytes.

不能在 ORDER BY 子句中使用 ntext、text、image、geography、geometry 和 xml 类型的列 。Columns of type ntext, text, image, geography, geometry, and xml cannot be used in an ORDER BY clause.

order_by_expression 出现在排名函数中时,无法指定整数或常量 。An integer or constant cannot be specified when order_by_expression appears in a ranking function. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

如果已在 FROM 子句中指定了表名的别名,则在 ORDER BY 子句中只能使用该别名来限定其列。If a table name is aliased in the FROM clause, only the alias name can be used to qualify its columns in the ORDER BY clause.

如果 SELECT 语句包含以下子句或运算符之一,则必须在选择列表中定义在 ORDER BY 子句中指定的列名和别名:Column names and aliases specified in the ORDER BY clause must be defined in the select list if the SELECT statement contains one of the following clauses or operators:

  • UNION 运算符UNION operator

  • EXCEPT 运算符EXCEPT operator

  • INTERSECT 运算符INTERSECT operator

  • SELECT DISTINCTSELECT DISTINCT

此外,当语句包含 UNION、EXCEPT 或 INTERSECT 运算符时,必须在第一个(左侧)查询的选择列表中指定列名或列别名。Additionally, when the statement includes a UNION, EXCEPT, or INTERSECT operator, the column names, or column aliases must be specified in the select list of the first (left-side) query.

在使用 UNION、EXCEPT 或 INTERSECT 运算符的查询中,只允许在语句末尾使用 ORDER BY。In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY is allowed only at the end of the statement. 只有在顶级查询而不是子查询中指定了 UNION、EXCEPT 和 INTERSECT 时,此限制才适用。This restriction applies only to when you specify UNION, EXCEPT, and INTERSECT in a top-level query and not in a subquery. 请参阅后面的“示例”一节。See the Examples section that follows.

除非还指定了 TOP 子句或 OFFSET 和 FETCH 子句,否则,视图、内联函数、派生表和子查询中的 ORDER BY 子句无效。The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. 在这些对象中使用 ORDER BY 时,该子句仅用于确定由 TOP 子句或 OFFSET 和 FETCH 子句返回的行。When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. ORDER BY 不保证在查询这些构造时得到有序结果,除非在查询本身中也指定了 ORDER BY。The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

索引视图或使用 CHECK OPTION 子句定义的视图中不支持 OFFSET 和 FETCH。OFFSET and FETCH are not supported in indexed views or in a view that is defined by using the CHECK OPTION clause.

可以在允许 TOP 和 ORDER BY 的任何查询中使用 OFFSET 和 FETCH,但具有以下限制:OFFSET and FETCH can be used in any query that allows TOP and ORDER BY with the following limitations:

  • OVER 子句不支持 OFFSET 和 FETCH。The OVER clause does not support OFFSET and FETCH.

  • 无法在 INSERT、UPDATE、MERGE 和 DELETE 语句中直接指定 OFFSET 和 FETCH,但可以在这些语句定义的子查询中指定 OFFSET 和 FETCH。OFFSET and FETCH cannot be specified directly in INSERT, UPDATE, MERGE, and DELETE statements, but can be specified in a subquery defined in these statements. 例如,在 INSERT INTO SELECT 语句中,可以在 SELECT 语句中指定 OFFSET 和 FETCH。For example, in the INSERT INTO SELECT statement, OFFSET and FETCH can be specified in the SELECT statement.

  • 在使用 UNION、EXCEPT 或 INTERSECT 运算符的查询中,只能在指定查询结果顺序的最终查询中指定 OFFSET 和 FETCH。In a query that uses UNION, EXCEPT or INTERSECT operators, OFFSET and FETCH can only be specified in the final query that specifies the order of the query results.

  • TOP 不能与 OFFSET 和 FETCH 在同一个查询表达式(同一个查询作用域)中结合使用。TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope).

使用 OFFSET 和 FETCH 限制返回的行数Using OFFSET and FETCH to limit the rows returned

建议您使用 OFFSET 和 FETCH 子句而不是 TOP 子句实现查询分页解决方案,并限制发送到客户端应用程序的行数。We recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application.

如果将 OFFSET 和 FETCH 作为分页解决方案,则需要为返回到客户端应用程序的每“页”数据运行一次查询。Using OFFSET and FETCH as a paging solution requires running the query one time for each "page" of data returned to the client application. 例如,要以 10 行为增量返回查询结果,您必须执行一次查询以返回 1-10 行,然后再次运行查询以返回 11- 20 行,依此类推。For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. 每个查询都是独立的,不会以任何方式与其他查询相关联。Each query is independent and not related to each other in any way. 这意味着,与使用执行一次查询并在服务器上保持状态的游标不同,将由客户端应用程序负责跟踪状态。This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state. 若要使用 OFFSET 和 FETCH 在查询请求之间获得稳定的结果,必须满足以下条件:To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

  1. 查询使用的基础数据不能发生变化。The underlying data that is used by the query must not change. 即,不会更新查询处理的行,也不会在单个事务中使用快照或可序列化事务隔离执行查询中的所有页面请求。That is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a single transaction using either snapshot or serializable transaction isolation. 有关这些事务隔离级别的详细信息,请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)For more information about these transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  2. ORDER BY 子句包含保证是唯一的列或列组合。The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

请参阅本主题后面的“示例”部分中的“在单个事务中运行多个查询”示例。See the example "Running multiple queries in a single transaction" in the Examples section later in this topic.

如果一致的执行计划在分页解决方案中至关重要,应考虑使用 OFFSET 和 FETCH 参数的 OPTIMIZE FOR 查询提示。If consistent execution plans are important in your paging solution, consider using the OPTIMIZE FOR query hint for the OFFSET and FETCH parameters. 请参阅本主题后面的“示例”部分中的“指定 OFFSET 和 FETCH 值的表达式”。See "Specifying expressions for OFFSET and FETCH values" in the Examples section later in this topic. 有关 OPTIMIZE FOR 的详细信息,请参阅查询提示 (Transact-SQL)For more information about OPTIMIZE FOR, see Query Hints (Transact-SQL).

示例Examples

类别Category 作为特征的语法元素Featured syntax elements
基本语法Basic syntax ORDER BYORDER BY
指定升序和降序Specifying ascending and descending order DESC • ASCDESC • ASC
指定排序规则Specifying a collation COLLATECOLLATE
指定条件顺序Specifying a conditional order CASE 表达式CASE expression
在排名函数中使用 ORDER BYUsing ORDER BY in a ranking function 排名函数Ranking functions
限制返回的行数Limiting the number of rows returned OFFSET • FETCHOFFSET • FETCH
将 ORDER BY 与 UNION、EXCEPT 和 INTERSECT 一起使用Using ORDER BY with UNION, EXCEPT, and INTERSECT UNIONUNION

基本语法Basic syntax

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

A.A. 指定在选择列表中定义的单个列Specifying a single column defined in the select list

以下示例按数值 ProductID 列对结果集进行顺序。The following example orders the result set by the numeric ProductID column. 由于未指定特定的排序顺序,将使用默认顺序(升序)。Because a specific sort order is not specified, the default (ascending order) is used.

USE AdventureWorks2012;  
GO  
SELECT ProductID, Name FROM Production.Product  
WHERE Name LIKE 'Lock Washer%'  
ORDER BY ProductID;  

B.B. 指定未在选择列表中定义的列Specifying a column that is not defined in the select list

以下示例按未包含在选择列表中的列对结果集进行排序,但 FROM 子句中指定的表中定义了该列。The following example orders the result set by a column that is not included in the select list, but is defined in the table specified in the FROM clause.

USE AdventureWorks2012;  
GO  
SELECT ProductID, Name, Color  
FROM Production.Product  
ORDER BY ListPrice;  
  

C.C. 将别名指定为排序列Specifying an alias as the sort column

以下示例将列别名 SchemaName 指定为排序顺序列。The following example specifies the column alias SchemaName as the sort order column.

USE AdventureWorks2012;  
GO  
SELECT name, SCHEMA_NAME(schema_id) AS SchemaName  
FROM sys.objects  
WHERE type = 'U'  
ORDER BY SchemaName;  
  

D.D. 将表达式指定为排序列Specifying an expression as the sort column

以下示例将表达式作为排序列。The following example uses an expression as the sort column. 表达式是使用 DATEPART 函数定义的,以便按员工的雇用年份对结果集进行排序。The expression is defined by using the DATEPART function to sort the result set by the year in which employees were hired.

USE AdventureWorks2012;  
GO  
SELECT BusinessEntityID, JobTitle, HireDate  
FROM HumanResources.Employee  
ORDER BY DATEPART(year, HireDate);  
  

指定升序和降序排序顺序Specifying ascending and descending sort order

A.A. 指定降序Specifying a descending order

以下示例按 ProductID 数值列降序对结果集进行排序。The following example orders the result set by the numeric column ProductID in descending order.

USE AdventureWorks2012;  
GO  
SELECT ProductID, Name FROM Production.Product  
WHERE Name LIKE 'Lock Washer%'  
ORDER BY ProductID DESC;  
  

B.B. 指定升序Specifying an ascending order

以下示例按 Name 列升序对结果集进行排序。The following example orders the result set by the Name column in ascending order. 字符按字母顺序排序,而不是数值顺序。The characters are sorted alphabetically, not numerically. 也就是说,10 排在 2 之前。That is, 10 sorts before 2.

USE AdventureWorks2012;  
GO  
SELECT ProductID, Name FROM Production.Product  
WHERE Name LIKE 'Lock Washer%'  
ORDER BY Name ASC ;  
  

C.C. 同时指定升序和降序Specifying both ascending and descending order

以下示例按两个列对结果集进行排序。The following example orders the result set by two columns. 先按 FirstName 列升序对查询结果集进行排序,然后按 LastName 列降序进行排序。The query result set is first sorted in ascending order by the FirstName column and then sorted in descending order by the LastName column.

USE AdventureWorks2012;  
GO  
SELECT LastName, FirstName FROM Person.Person  
WHERE LastName LIKE 'R%'  
ORDER BY FirstName ASC, LastName DESC ;  
  

指定排序规则Specifying a collation

以下示例说明如何在 ORDER BY 子句中指定排序规则以更改查询结果的返回顺序。The following example shows how specifying a collation in the ORDER BY clause can change the order in which the query results are returned. 将创建一个表,其中包含一个使用不区分大小写和重音的排序规则定义的列。A table is created that contains a column defined by using a case-insensitive, accent-insensitive collation. 插入一些具有不同大小写和重音的值。Values are inserted with a variety of case and accent differences. 由于未在 ORDER BY 子句中指定排序规则,在对值进行排序时,第一个查询将使用列排序规则。Because a collation is not specified in the ORDER BY clause, the first query uses the collation of the column when sorting the values. 在第二个查询中,在 ORDER BY 子句中指定了区分大小写和重音的排序规则,这将改变行的返回顺序。In the second query, a case-sensitive, accent-sensitive collation is specified in the ORDER BY clause, which changes the order in which the rows are returned.

USE tempdb;  
GO  
CREATE TABLE #t1 (name nvarchar(15) COLLATE Latin1_General_CI_AI)  
GO  
INSERT INTO #t1 VALUES(N'Sánchez'),(N'Sanchez'),(N'sánchez'),(N'sanchez');  
  
-- This query uses the collation specified for the column 'name' for sorting.  
SELECT name  
FROM #t1  
ORDER BY name;  
-- This query uses the collation specified in the ORDER BY clause for sorting.  
SELECT name  
FROM #t1  
ORDER BY name COLLATE Latin1_General_CS_AS;  
  

指定条件顺序Specifying a conditional order

以下示例在 ORDER BY 子句中使用 CASE 表达式,以根据给定的列值有条件地确定行的排序顺序。The following examples use the CASE expression in an ORDER BY clause to conditionally determine the sort order of the rows based on a given column value. 在第一个示例中,会计算 SalariedFlag 表中 HumanResources.Employee 列的值。In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. SalariedFlag 设置为 1 的员工将按 BusinessEntityID 以降序顺序返回。Employees that have the SalariedFlag set to 1 are returned in order by the BusinessEntityID in descending order. SalariedFlag 设置为 0 的员工将按 BusinessEntityID 以升序顺序返回。Employees that have the SalariedFlag set to 0 are returned in order by the BusinessEntityID in ascending order. 在第二个示例中,当 TerritoryName 列等于“United States”时,结果集会按 CountryRegionName 列排序,对于所有其他行则按 CountryRegionName 排序。In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.

SELECT BusinessEntityID, SalariedFlag  
FROM HumanResources.Employee  
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;  
GO  
  
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL  
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName  
         ELSE CountryRegionName END;  
  

在排名函数中使用 ORDER BYUsing ORDER BY in a ranking function

以下示例在 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE 排名函数中使用 ORDER BY 子句。The following example uses the ORDER BY clause in the ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

USE AdventureWorks2012;  
GO  
SELECT p.FirstName, p.LastName  
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"  
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"  
    ,s.SalesYTD, a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;  
  

限制返回的行数Limiting the number of rows returned

以下示例使用 OFFSET 和 FETCH 限制查询返回的行数。The following examples use OFFSET and FETCH to limit the number of rows returned by a query.

适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL ServerSQL ServerAzure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database.

A.A. 指定整数常量以提供 OFFSET 和 FETCH 值Specifying integer constants for OFFSET and FETCH values

以下示例将一个整数常量指定为 OFFSET 和 FETCH 子句的值。The following example specifies an integer constant as the value for the OFFSET and FETCH clauses. 第一个查询返回所有按 DepartmentID 列排序的行。The first query returns all rows sorted by the column DepartmentID. 将此查询返回的结果与后面的两个查询的结果进行比较。Compare the results returned by this query with the results of the two queries that follow it. 下一个查询使用 OFFSET 5 ROWS 子句跳过前 5 行,然后返回所有其余行。The next query uses the clause OFFSET 5 ROWS to skip the first 5 rows and return all remaining rows. 最终查询使用 OFFSET 0 ROWS 子句从第一行开始,然后使用 FETCH NEXT 10 ROWS ONLY 将返回的行限制为排序的结果集中的 10 行。The final query uses the clause OFFSET 0 ROWS to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

USE AdventureWorks2012;  
GO  
-- Return all rows sorted by the column DepartmentID.  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID;  
  
-- Skip the first 5 rows from the sorted result set and return all remaining rows.  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID OFFSET 5 ROWS;  
  
-- Skip 0 rows and return only the first 10 rows from the sorted result set.  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID   
    OFFSET 0 ROWS  
    FETCH NEXT 10 ROWS ONLY;  
  

B.B. 指定变量以提供 OFFSET 和 FETCH 值Specifying variables for OFFSET and FETCH values

下面的示例声明 @StartingRowNumber@FetchRows 变量,并在 OFFSET 和 FETCH 子句中指定这些变量。The following example declares the variables @StartingRowNumber and @FetchRows and specifies these variables in the OFFSET and FETCH clauses.

USE AdventureWorks2012;  
GO  
-- Specifying variables for OFFSET and FETCH values    
DECLARE @StartingRowNumber tinyint = 1  
      , @FetchRows tinyint = 8;  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID ASC   
    OFFSET @StartingRowNumber ROWS   
    FETCH NEXT @FetchRows ROWS ONLY;  
  

C.C. 指定表达式以提供 OFFSET 和 FETCH 值Specifying expressions for OFFSET and FETCH values

下面的示例使用 @StartingRowNumber - 1 表达式指定 OFFSET 值,并使用 @EndingRowNumber - @StartingRowNumber + 1 表达式指定 FETCH 值。The following example uses the expression @StartingRowNumber - 1 to specify the OFFSET value and the expression @EndingRowNumber - @StartingRowNumber + 1 to specify the FETCH value. 另外,还指定了查询提示 OPTIMIZE FOR。In addition, the query hint, OPTIMIZE FOR, is specified. 在编译和优化查询时,可以使用此提示为局部变量提供特定的值。This hint can be used to provide a particular value for a local variable when the query is compiled and optimized. 仅在查询优化期间使用该值,在查询执行期间不使用该值。The value is used only during query optimization, and not during query execution. 有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

USE AdventureWorks2012;  
GO  
  
-- Specifying expressions for OFFSET and FETCH values      
DECLARE @StartingRowNumber tinyint = 1  
      , @EndingRowNumber tinyint = 8;  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID ASC   
    OFFSET @StartingRowNumber - 1 ROWS   
    FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY  
OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );  
  

D.D. 指定常数标量子查询以提供 OFFSET 和 FETCH 值Specifying a constant scalar subquery for OFFSET and FETCH values

以下示例使用常数标量子查询定义 FETCH 子句的值。The following example uses a constant scalar subquery to define the value for the FETCH clause. 该子查询从 PageSize 表的 dbo.AppSettings 列中返回单个值。The subquery returns a single value from the column PageSize in the table dbo.AppSettings.

-- Specifying a constant scalar subquery  
USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);  
GO  
INSERT INTO dbo.AppSettings VALUES(1, 10);  
GO  
DECLARE @StartingRowNumber tinyint = 1;  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID ASC   
    OFFSET @StartingRowNumber ROWS   
    FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;  

E.E. 在单个事务中运行多个查询Running multiple queries in a single transaction

以下示例说明一种实现分页解决方案的方法,以确保在查询的所有请求中返回稳定的结果。The following example shows one method of implementing a paging solution that ensures stable results are returned in all requests from the query. 查询在使用快照隔离级别的单个事务中执行,同时,ORDER BY 语句中指定的列确保列的唯一性。The query is executed in a single transaction using the snapshot isolation level, and the column specified in the ORDER BY clause ensures column uniqueness.

USE AdventureWorks2012;  
GO  
  
-- Ensure the database can support the snapshot isolation level set for the query.  
IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2012') = 0  
    ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;  
GO  
  
-- Set the transaction isolation level  to SNAPSHOT for this query.  
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
GO  
  
-- Beginning the transaction.
BEGIN TRANSACTION;  
GO  
-- Declare and set the variables for the OFFSET and FETCH values.  
DECLARE @StartingRowNumber int = 1  
      , @RowCountPerPage int = 3;  
  
-- Create the condition to stop the transaction after all rows have been returned.  
WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber  
BEGIN  
  
-- Run the query until the stop condition is met.  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID ASC   
    OFFSET @StartingRowNumber - 1 ROWS   
    FETCH NEXT @RowCountPerPage ROWS ONLY;  
  
-- Increment @StartingRowNumber value.  
SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;  
CONTINUE  
END;  
GO  
COMMIT TRANSACTION;  
GO  

将 ORDER BY 与 UNION、EXCEPT 和 INTERSECT 一起使用Using ORDER BY with UNION, EXCEPT, and INTERSECT

当查询使用 UNION、EXCEPT 或 INTERSECT 运算符时,必须在语句末尾指定 ORDER BY 子句,并对合并的查询结果进行排序。When a query uses the UNION, EXCEPT, or INTERSECT operators, the ORDER BY clause must be specified at the end of the statement and the results of the combined queries are sorted. 以下示例返回所有红色或黄色的产品,并按 ListPrice 列对合并的列表进行排序。The following example returns all products that are red or yellow and sorts this combined list by the column ListPrice.

USE AdventureWorks2012;  
GO  
SELECT Name, Color, ListPrice  
FROM Production.Product  
WHERE Color = 'Red'  
-- ORDER BY cannot be specified here.  
UNION ALL  
SELECT Name, Color, ListPrice  
FROM Production.Product  
WHERE Color = 'Yellow'  
ORDER BY ListPrice ASC;  

示例: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

以下示例演示按 EmployeeKey 数值列升序对结果集进行排序。The following example demonstrates ordering of a result set by the numerical EmployeeKey column in ascending order.

-- Uses AdventureWorks  
  
SELECT EmployeeKey, FirstName, LastName FROM DimEmployee  
WHERE LastName LIKE 'A%'  
ORDER BY EmployeeKey;  

以下示例按 EmployeeKey 数值列降序对结果集进行排序。The following example orders a result set by the numerical EmployeeKey column in descending order.

-- Uses AdventureWorks  
  
SELECT EmployeeKey, FirstName, LastName FROM DimEmployee  
WHERE LastName LIKE 'A%'  
ORDER BY EmployeeKey DESC;  

以下示例按 LastName 列对结果集进行排序。The following example orders a result set by the LastName column.

-- Uses AdventureWorks  
  
SELECT EmployeeKey, FirstName, LastName FROM DimEmployee  
WHERE LastName LIKE 'A%'  
ORDER BY LastName;  

以下示例按照两列进行排序。The following example orders by two columns. 此查询首先按 FirstName 列以升序排序,然后按 LastName 列以降序对常见 FirstName 值降序排序。This query first sorts in ascending order by the FirstName column, and then sorts common FirstName values in descending order by the LastName column.

-- Uses AdventureWorks  
  
SELECT EmployeeKey, FirstName, LastName FROM DimEmployee  
WHERE LastName LIKE 'A%'  
ORDER BY LastName, FirstName;  

另请参阅See Also

表达式 (Transact-SQL) Expressions (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
FROM (Transact-SQL) FROM (Transact-SQL)
排名函数 (Transact-SQL) Ranking Functions (Transact-SQL)
TOP (Transact-SQL) TOP (Transact-SQL)
查询提示 (Transact-SQL) Query Hints (Transact-SQL)
EXCEPT 和 INTERSECT (Transact-SQL) EXCEPT and INTERSECT (Transact-SQL)
UNION (Transact-SQL) UNION (Transact-SQL)
CASE (Transact-SQL)CASE (Transact-SQL)