SELECT - ORDER BY 子句 (Transact-SQL)SELECT - ORDER BY Clause (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse 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 SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲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 SQL 資料倉儲Azure SQL Data Warehouse 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_expressionorder_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_nameCOLLATE 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 只適用於下列類型的資料行:charvarcharncharnvarcharCOLLATE 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 Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017 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 } ONLYFETCH { 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. 值可以是大於或等於一的整數常數或運算式。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 Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017 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 查詢運算子的 RowsTop 屬性中。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,並不會變更檢視表的可更新性屬性。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 子句中不能使用 ntexttextimagegeographygeometryxml 類型的資料行。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.

  • OFFSET 和 FETCH 不能直接在 INSERT、UPDATE、MERGE 和 DELETE 陳述式中指定,但是可以在這些陳述式中所定義的子查詢中指定。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 陳述式中,OFFSET 和 FETCH 可以在 SELECT 陳述式中指定。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
搭配 UNION、EXCEPT 和 INTERSECT 使用 ORDER BYUsing 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 排序 (當資料行 CountryRegionName 等於 'United States' 時) 以及依照 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 Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017 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  
  
-- Beging 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 BYUsing 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 SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse 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)