共用方式為


SELECT - ORDER BY 子句 (Transact-SQL)

適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

排序 SQL Server 中查詢所傳回的資料。 此子句可用於:

  • 依據指定的資料行清單排序查詢的結果集,並選擇性地將傳回的資料列限制在指定範圍內。 除非指定 子句,否則 ORDER BY 不會保證在結果集中傳回數據列的順序。

  • 決定次序函數值套用至結果集的順序。

Transact-SQL 語法慣例

注意

ORDER BYAzure Synapse Analytics 或 Analytics Platform System (PDW) 中的 或 (CTAS) 語句不支援 SELECTINTO/。CREATE TABLE AS SELECT

語法

SQL Server 和 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
    ]
}

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_constant | offset_row_count_expression } { ROW | ROWS }

適用於:SQL Server 2012 (11.x) 和更新版本、Azure SQL 資料庫 和 Azure SQL 受控執行個體。

指定要略過的資料列數目,然後才開始從查詢運算式傳回資料列。 值可以是大於或等於零的整數常數或運算式。

offset_row_count_expression 可以是變數、參數或常數純量子查詢。 使用子查詢時,它無法參考外部查詢範圍中定義的任何數據行。 也就是說,它無法與外部查詢相互關聯。

ROWROWS 是同義字,並提供 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 處理 子句之後傳回的數據列數目。 值可以是大於或等於一的整數常數或運算式。

fetch_row_count_expression 可以是變數、參數或常數純量子查詢。 使用子查詢時,它無法參考外部查詢範圍中定義的任何數據行。 也就是說,它無法與外部查詢相互關聯。

FIRSTNEXT 是同義字,並提供 ANSI 相容性。

ROWROWS 是同義字,並提供 ANSI 相容性。

在查詢執行計劃中,位移數據列計數值會顯示在查詢運算子的 TOP Rows 或 Top 屬性中。

最佳作法

請避免將 子句中的 ORDER BY 整數指定為選取清單中數據行的位置表示法。 例如,雖然 之類的 SELECT ProductID, Name FROM Production.Production ORDER BY 2 語句有效,但相較於指定實際數據行名稱,其他語句並不容易理解。 此外,選取清單的變更,例如變更數據行順序或新增數據行,需要修改 ORDER BY 子句,以避免非預期的結果。

在語句中 SELECT TOP (<n>) ,一律使用 ORDER BY 子句。 這是可預測地指出哪些數據列受到 TOP影響的唯一方法。 如需詳細資訊,請參閱 TOP

互通性

搭配 SELECT...INTOINSERT...SELECT 語句使用 來插入來自另一個來源的數據列時, ORDER BY 子句不保證數據列會以指定的順序插入。

OFFSET 檢視中使用 和 FETCH 並不會變更檢視的可更新性屬性。

限制

子句中的數據 ORDER BY 行數目沒有限制。 不過,子句中指定的 ORDER BY 數據行大小總計不能超過8,060個字節。

類型為 ntext、textimagegeographygeometryxml 的數據行不能用於 子句中ORDER BY

當排名函式中出現order_by_expression,無法指定整數或常數。 如需詳細資訊,請參閱 SELECT - OVER 子句

如果數據表名稱在 子句中 FROM 為別名,則只能使用別名名稱來限定子句中的數據 ORDER BY 行。

如果SELECT語句包含下列其中一個子句或運算子,則必須在選取清單中定義 子句中指定的ORDER BY資料行名稱和別名:

  • UNION 算子
  • EXCEPT 算子
  • INTERSECT 算子
  • SELECT DISTINCT

此外,當語句包含 、 EXCEPTINTERSECT 運算子時,必須在第一個 UNION(左側) 查詢的選取清單中指定資料行名稱或數據行別名。

在使用 UNIONEXCEPTINTERSECT 運算子的查詢中, ORDER BY 只允許在 語句結尾。 只有在您在最上層查詢中指定 UNIONEXCEPTINTERSECT ,而不是在子查詢中時,才會套用此限制。 請參閱後續的<範例>一節。

ORDER BY 句在檢視、內嵌函式、衍生數據表和子查詢中無效,除非 TOP 同時指定 或 OFFSETFETCH 子句。 當ORDER BY用於這些物件時,子句只會用來判斷 子句或 OFFSETFETCH 子句所TOP傳回的數據列。 ORDER BY子句不保證查詢這些建構時的已排序結果,除非ORDER BY查詢本身也指定。

OFFSETFETCH 不支援在索引檢視表或使用 子句定義的檢視中 CHECK OPTION

OFFSETFETCH 可用於允許 TOPORDER BY 具有下列限制的任何查詢:

  • OVER 句不支援 OFFSETFETCH

  • OFFSETFETCH 不能直接在 、 UPDATEMERGEDELETE 語句中INSERT指定,但可以在這些語句中定義的子查詢中指定。 例如,在語句中INSERT INTO SELECTOFFSET可以在語句中SELECT指定 和 FETCH

  • 在使用、 EXCEPT 或運算子的查詢UNION中,FETCHOFFSET只能在指定查詢結果順序的最終查詢中INTERSECT指定。

  • TOP 無法在相同的查詢表達式中與 OFFSETFETCH 結合(在相同的查詢範圍中)。

使用 OFFSET 和 FETCH 來限制傳回的數據列

您應該使用 OFFSETFETCH 子句,而不是 TOP 子句來實作查詢分頁解決方案,並限制傳送至用戶端應用程式的數據列數目。

使用 OFFSETFETCH 作為分頁解決方案需要針對傳回至用戶端應用程式的每個 數據頁面 執行一次查詢。 例如,若要以 10 個數據列遞增傳回查詢的結果,您必須一次執行查詢,以傳回數據列 1 到 10,然後再次執行查詢以傳回數據列 11 到 20,依此等。 每個查詢各自獨立,無論如何都不相關。 這表示,不同於使用資料指標執行查詢一次,並在伺服器上維護狀態,用戶端應用程式會負責追蹤狀態。 若要使用 OFFSETFETCH達到查詢要求之間的穩定結果,必須符合下列條件:

  1. 查詢所使用的基礎資料不可以變更。 也就是說,查詢所觸及的數據列不會更新,或是查詢中頁面的所有要求都會使用快照集或可串行化的交易隔離,在單一交易中執行。 如需這些交易隔離等級的詳細資訊,請參閱 SET TRANSACTION ISOLATION LEVEL

  2. ORDER BY 句包含保證是唯一的數據行或數據行組合。

請參閱本文稍後的一節中的範例。

如果您的分頁解決方案中一致執行計劃很重要,請考慮使用 和 FETCH 參數的OPTIMIZE FOROFFSET查詢提示。 請參閱本文稍後的<範例>一節中的指定 OFFSET 和 FETCH 值的表達式。 如需 的詳細資訊 OPTIMIZE FOR,請參閱 查詢提示

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022AdventureWorksDW2022 範例資料庫,從 Microsoft SQL Server Samples 和 Community Projects (Microsoft SQL Server 範例和社群專案)首頁即可下載。

類別 代表性語法元素
基本語法 ORDER BY
指定遞增和遞減順序 DESCASC
指定定序 COLLATE
指定條件式順序 CASE 運算式
在排名函式中使用 ORDER BY 次序函數
限制傳回的數據列數目 OFFSETFETCH
搭配 UNION、EXCEPT 和 INTERSECT 使用 ORDER BY 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 排序 (當資料行 CountryRegionName 等於 'United States' 時) 以及依照 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_RANK、 和NTILE中的 子句。

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 值的整數常數

下列範例會將整數常數指定為 和 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和 ,並在和 @FetchRows FETCH 子句中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

搭配 UNION、EXCEPT 和 INTERSECT 使用 ORDER BY

當查詢使用 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 資料行以遞增順序排序,然後再依據 LastName 資料行以遞減順序排序共同的 FirstName 值。

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