SELECT - ORDER BY 子句 (Transact-SQL)

适用于: SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics 分析平台系统 (PDW) Microsoft Fabric 中的 SQL 分析端点 Microsoft Fabric 中的仓库

对 SQL Server 中的查询返回的数据进行排序。 可以使用此子句执行以下操作:

  • 按指定的列列表对查询的结果集进行排序,并有选择地将返回的行限制为指定范围。 除非 ORDER BY 指定了子句,否则不保证在结果集中返回行的顺序。

  • 确定将排名函数值应用于结果集的顺序。

Transact-SQL 语法约定

注意

ORDER BY Azure Synapse Analytics 或 Analytics 平台系统(PDW)中的 SELECT/INTO 语句或 CREATE TABLE AS SELECT (CTAS) 语句不受支持。

语法

SQL Server 和 Azure SQL 数据库 的语法。

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
    ]
}

Azure Synapse Analytics 和并行数据仓库的语法。

[ ORDER BY
    {
    order_by_expression
    [ ASC | DESC ]
    } [ , ...n ]
]

参数

order_by_expression

指定用于对查询结果集进行排序的列或表达式。 可以将排序列指定为名称或列别名,或表示列在选择列表中位置的非负整数。

可以指定多个排序列。 别名必须是唯一的。 子句中 ORDER BY 排序列的顺序定义排序结果集的组织。 也就是说,按第一列对结果集进行排序,然后按第二列对排序列表进行排序,依此类推。

子句中引用的 ORDER BY 列名必须与选择列表中的列或列别名或子句中指定的 FROM 表中定义的列相对应,且没有任何歧义。 ORDER BY如果子句从选择列表中引用列别名,则列别名必须单独使用,而不是作为子句中ORDER BY某些表达式的一部分,例如:

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

指定ORDER BY应根据collation_name中指定的排序规则执行该操作,而不是根据表或视图中定义的列的排序规则执行。 collation_name可以是 Windows 排序规则名称或 SQL 排序规则名称。 有关详细信息,请参阅 排序规则和 Unicode 支持COLLATE仅适用于 char、varchar、ncharnvarchar 类型的列。

ASC | DESC

指定应当按升序或降序对指定列中的值进行排序。 ASC 将按照从最低值到最高值的顺序排序。 DESC 将按照从最高值到最低值的顺序排序。 ASC 是默认排序顺序。 NULL 值被视为最低的可能值。

OFFSET { integer_constantoffset_row_count_expression } { ROW | ROWS } |

适用于:SQL Server 2012(11.x)及更高版本、Azure SQL 数据库和Azure SQL 托管实例。

指定开始从查询表达式返回行之前跳过的行数。 该值可以是大于或等于零的整数常量或表达式。

offset_row_count_expression 可以是变量、参数或常量标量子查询 。 使用子查询时,它不能引用在外部查询范围中定义的任何列。 也就是说,它不能与外部查询相关联。

ROW 并且 ROWS 是同义词,用于 ANSI 兼容性。

在查询执行计划中,偏移行计数值显示在查询运算符的 TOP Offset 属性中

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

适用于:SQL Server 2012(11.x)及更高版本、Azure SQL 数据库和Azure SQL 托管实例。

指定要在处理子句后返回的 OFFSET 行数。 该值可以是大于或等于 1 的整数常量或表达式。

fetch_row_count_expression 可以是变量、参数或常量标量子查询 。 使用子查询时,它不能引用在外部查询范围中定义的任何列。 也就是说,它不能与外部查询相关联。

FIRST 并且 NEXT 是同义词,用于 ANSI 兼容性。

ROW 并且 ROWS 是同义词,用于 ANSI 兼容性。

在查询执行计划中,偏移行计数值显示在查询运算符的 TOP RowsTop 属性中。

最佳做法

避免在 ORDER BY 子句中将整数指定为选择列表中的列的位置表示形式。 例如,尽管语句(如 SELECT ProductID, Name FROM Production.Production ORDER BY 2 有效),但与指定实际列名相比,其他语句并不容易理解。 此外,对选择列表的更改(如更改列顺序或添加新列)需要修改 ORDER BY 子句以避免意外结果。

SELECT TOP (<n>)在语句中,始终使用子ORDER BY句。 这是可预测地指示受哪些行影响的 TOP唯一方法。 有关更多信息,请参见 TOP

互操作性

与某个或INSERT...SELECT语句一起使用SELECT...INTO以插入来自其他源的行时,子ORDER BY句不保证按指定顺序插入行。

使用 OFFSETFETCH 在视图中不会更改视图的可更新性属性。

限制

子句中的 ORDER BY 列数没有限制。 但是,子 ORDER BY 句中指定的列的总大小不能超过 8,060 字节。

不能在子句中使用 ntext、textimagegeographygeometryxml 类型的ORDER BY列。

当order_by_expression出现在排名函数中时,无法指定整数或常量。 有关详细信息,请参阅 SELECT - OVER 子句

如果表名在子句中 FROM 具有别名,则只能使用别名来限定子句中的 ORDER BY 列。

如果SELECT语句包含以下子句或运算符之一,则必须在 select 列表中定义子句中指定的ORDER BY列名和别名:

  • UNION 运算符
  • EXCEPT 运算符
  • INTERSECT 运算符
  • SELECT DISTINCT

此外,当语句包含一个 UNIONEXCEPTINTERSECT 运算符时,必须在第一个(左侧)查询的选择列表中指定列名或列别名。

在只允许在语句末尾使用 UNIONEXCEPTINTERSECT 运算符 ORDER BY 的查询中。 此限制仅适用于指定 UNIONEXCEPT在顶级查询中, INTERSECT 而不是子查询时。 请参阅下面的“示例”部分。

ORDER BY 句在视图、内联函数、派生表和子查询中无效,除非 TOP 同时指定或 OFFSETFETCH 句。 在这些对象中使用时ORDER BY,子句仅用于确定子句或OFFSETFETCH句返回TOP的行。 ORDER BY除非查询本身中也指定了子句,否则ORDER BY子句不能保证查询这些构造时的有序结果。

OFFSET在索引视图或通过使用CHECK OPTION子句定义的视图中不支持和FETCH

OFFSETFETCH 可用于允许 TOPORDER BY 具有以下限制的任何查询:

  • OVER 句不支持 OFFSETFETCH.

  • OFFSET 不能 FETCH 直接在 INSERTUPDATEMERGEDELETE 语句中指定,但可以在这些语句中定义的子查询中指定。 例如,在 INSERT INTO SELECT 语句中, OFFSET 可以在 FETCH 语句中 SELECT 指定。

  • 在使用UNIONINTERSECTEXCEPT运算符的查询中, OFFSET FETCH只能在指定查询结果顺序的最终查询中指定。

  • TOP不能与同一查询表达式(FETCH在同一查询范围内)结合使用OFFSET

使用 OFFSET 和 FETCH 限制返回的行

应使用 OFFSET and FETCH 子句而不是 TOP 子句来实现查询分页解决方案,并限制发送到客户端应用程序的行数。

使用 OFFSETFETCH 作为分页解决方案需要对返回到客户端应用程序的每个 数据页 运行一次查询。 例如,若要以 10 行增量返回查询的结果,必须执行查询一次以返回行 1 到 10 行,然后再次运行查询以返回行 11 到 20,依此等。 每个查询都是独立的,不会以任何方式与其他查询相关联。 这意味着,与使用执行一次查询并在服务器上保持状态的游标不同,将由客户端应用程序负责跟踪状态。 若要在查询请求之间使用 OFFSETFETCH实现稳定结果,必须满足以下条件:

  1. 查询使用的基础数据不能发生变化。 也就是说,查询触摸的行不会更新,或者查询中页面的所有请求都使用快照或可序列化的事务隔离在单个事务中执行。 有关这些事务隔离级别的详细信息,请参阅 SET TRANSACTION ISOLATION LEVEL

  2. ORDER BY 句包含保证唯一的列或列的组合。

请参阅本文后面的“示例”部分中的示例“在单个事务中运行多个查询”。

如果分页解决方案中的一致执行计划很重要,请考虑对参数FETCH使用OPTIMIZE FOR查询提示OFFSET。 请参阅本文后面的“示例”部分中的 OFFSET 和 FETCH 值的指定表达式。 有关详细信息 OPTIMIZE FOR,请参阅 查询提示

示例

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022AdventureWorksDW2022 示例数据库,可从 Microsoft SQL Server 示例和社区项目主页下载它。

类别 作为特征的语法元素
基本语法 ORDER BY
指定升序和降序 DESCASC
指定排序规则 COLLATE
指定条件顺序 CASE 表达式
在排名函数中使用 ORDER BY 排名函数
限制返回的行数 OFFSETFETCH
将 ORDER BY 与 UNION、EXCEPT 和 INTERSECT 配合使用 UNION

基本语法

本节中的示例演示了使用最低所需语法的 ORDER BY 子句的基本功能。

A. 指定在选择列表中定义的单个列

以下示例按数值 ProductID 列对结果集进行顺序。 由于未指定特定的排序顺序,因此使用默认的(升序顺序)。

USE AdventureWorks2022;
GO

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

B. 指定在选择列表中未定义的列

以下示例按不在选择列表中包含的列对结果集进行排序,但在子句中指定的 FROM 表中定义。

USE AdventureWorks2022;
GO

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

°C 将别名指定为排序列

以下示例将列别名 SchemaName 指定为排序顺序列。

USE AdventureWorks2022;
GO

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

D. 将表达式指定为排序列

以下示例将表达式作为排序列。 该表达式是使用 DATEPART 函数根据员工雇用年份对结果集进行排序来定义的。

USE AdventureWorks2022;
GO

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

指定升序和降序排序顺序

A. 指定降序

以下示例按 ProductID 数值列降序对结果集进行排序。

USE AdventureWorks2022;
GO

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

B. 指定升序

以下示例按 Name 列升序对结果集进行排序。 字符按字母顺序排序,而不是数值顺序。 也就是说,10 排在 2 之前。

USE AdventureWorks2022;
GO

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

°C 同时指定升序和降序

以下示例按两个列对结果集进行排序。 先按 FirstName 列升序对查询结果集进行排序,然后按 LastName 列降序进行排序。

USE AdventureWorks2022;
GO

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

指定排序规则

以下示例演示如何在子句中 ORDER BY 指定排序规则可以更改查询结果的返回顺序。 将创建一个表,其中包含一个使用不区分大小写和重音的排序规则定义的列。 使用各种大小写和重音差异插入值。 由于子句中 ORDER BY 未指定排序规则,因此第一个查询在对值进行排序时使用列的排序规则。 在第二个查询中,子句中 ORDER BY 指定区分大小写、区分重音的排序规则,这会更改返回行的顺序。

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;

指定条件顺序

以下示例使用 CASE 子句中的 ORDER BY 表达式根据给定的列值有条件地确定行的排序顺序。 在第一个示例中,会计算 SalariedFlag 表中 HumanResources.Employee 列的值。 SalariedFlag 设置为 1 的员工将按 BusinessEntityID 以降序顺序返回。 SalariedFlag 设置为 0 的员工将按 BusinessEntityID 以升序顺序返回。 在第二个示例中,当 TerritoryName 列等于“United States”时,结果集会按 CountryRegionName 列排序,对于所有其他行则按 CountryRegionName 排序。

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 BY

以下示例使用ORDER BY排名函数ROW_NUMBERRANKDENSE_RANKNTILE.

USE AdventureWorks2022;
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;

限制返回的行数

适用于:SQL Server 2012(11.x)及更高版本、Azure SQL 数据库和Azure SQL 托管实例。

以下示例使用 OFFSETFETCH 限制查询返回的行数。

A. 指定 OFFSET 和 FETCH 值的整数常量

以下示例将整数常量指定为 and FETCH 子句的值OFFSET。 第一个查询返回所有按 DepartmentID 列排序的行。 将此查询返回的结果与后面的两个查询的结果进行比较。 下一个查询使用子句 OFFSET 5 ROWS 跳过前五行并返回所有剩余行。 最终查询使用 OFFSET 0 ROWS 子句从第一行开始,然后使用 FETCH NEXT 10 ROWS ONLY 将返回的行限制为排序的结果集中的 10 行。

USE AdventureWorks2022;
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. 指定 OFFSET 和 FETCH 值的变量

以下示例声明变量@RowsToSkip并在@FetchRowsFETCH子句中OFFSET指定这些变量。

USE AdventureWorks2022;
GO

-- Specifying variables for OFFSET and FETCH values
DECLARE
    @RowsToSkip TINYINT = 2,
    @FetchRows TINYINT = 8;

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC OFFSET @RowsToSkip ROWS
FETCH NEXT @FetchRows ROWS ONLY;

°C 指定 OFFSET 和 FETCH 值的表达式

以下示例使用表达式 @StartingRowNumber - 1 指定 OFFSET 值和表达式 @EndingRowNumber - @StartingRowNumber + 1 来指定 FETCH 值。 此外,还指定了查询提示 OPTIMIZE FOR。 在编译和优化查询时,可以使用此提示为局部变量提供特定的值。 仅在查询优化期间使用该值,在查询执行期间不使用该值。 有关详细信息,请参阅 查询提示

USE AdventureWorks2022;
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. 为 OFFSET 和 FETCH 值指定常量标量子查询

以下示例使用常量标量子查询来定义子句的值 FETCH 。 该子查询从 PageSize 表的 dbo.AppSettings 列中返回单个值。

-- Specifying a constant scalar subquery
USE AdventureWorks2022;
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. 在单个事务中运行多个查询

以下示例说明一种实现分页解决方案的方法,以确保在查询的所有请求中返回稳定的结果。 查询使用快照隔离级别在单个事务中执行,子句中指定的 ORDER BY 列可确保列唯一性。

USE AdventureWorks2022;
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'AdventureWorks2022'
) = 0
ALTER DATABASE AdventureWorks2022
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 配合使用

当查询使用UNIONEXCEPTINTERSECT运算符时,ORDER BY必须在语句末尾指定子句,并对组合查询的结果进行排序。 以下示例返回所有红色或黄色的产品,并按 ListPrice 列对合并的列表进行排序。

USE AdventureWorks2022;
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 和 Analytics Platform System (PDW)

以下示例演示按 EmployeeKey 数值列升序对结果集进行排序。

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

以下示例按 EmployeeKey 数值列降序对结果集进行排序。

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

以下示例按 LastName 列对结果集进行排序。

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

以下示例按照两列进行排序。 此查询首先按 FirstName 列以升序排序,然后按 FirstName 列以降序对常见 LastName 值降序排序。

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