TOP (Transact-SQL)

  • 發行項

在 SQL Server 2012 中，將查詢結果集中所傳回的資料列數限制為指定數目的資料列或是資料列的百分比。當 TOP 與 ORDER BY 子句一起使用時，結果集會限制為前 N 個經過排序的資料列，否則會傳回前 N 個未經任何方式排序的資料列。使用這個子句以指定 SELECT 陳述式所傳回的資料列數目或受到 INSERT、UPDATE、MERGE 或 DELETE 陳述式所影響的資料列數目。

主題連結圖示 Transact-SQL 語法慣例

語法

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

引數

  • expression
    這是指定要傳回的資料列數目之數值運算式。如果指定了 PERCENT，expression 會隱含地轉換為 float 值；否則，它會轉換為 bigint。

  • PERCENT
    指出查詢只從結果集中傳回前 expression % 的資料列。含小數的值會無條件進位到下一個整數值。

  • WITH TIES
    當您想要針對限制結果集中的最後一個位數傳回兩個或多個繫結的資料列時使用。必須搭配 ORDER BY 子句一起使用。WITH TIES 可能會導致傳回的資料列數，比 expression 中的指定值還多。例如，如果 expression 設定為 5，但 2 個額外的資料列符合資料列 5 中 ORDER BY 資料行的值，結果集就會包含 7 個資料列。

    只有在 SELECT 陳述式中，且只在指定了 ORDER BY 子句時，才能指定 TOP...WITH TIES。有同值記錄的傳回順序是任意的。ORDER BY 不會影響此規則。

最佳作法

在 SELECT 陳述式中，永遠搭配 TOP 子句使用 ORDER BY 子句。這是以預測的方式指示哪些資料列受到 TOP 影響的唯一方法。

在 ORDER BY 子句中使用 OFFSET 和 FETCH 子句 (而不要使用 TOP 子句)，來實作查詢分頁方案。使用 OFFSET 和 FETCH 子句，比較容易實作分頁方案 (也就是將資料區塊或「頁面」傳送到用戶端)。如需詳細資訊，請參閱＜ORDER BY 子句 (Transact-SQL)＞。

使用 TOP 或 OFFSET 和 FETCH (而不要使用 SET ROWCOUNT) 來限制傳回的資料列數目。這些方法優於 SET ROWCOUNT 用法，原因如下：

  • 在 SELECT 陳述式中，查詢最佳化工具在查詢最佳化期間會考慮 TOP 或 FETCH 子句中的 <expression> 值。因為 SET ROWCOUNT 是在執行查詢的陳述式之外使用，所以它的值不能在查詢計畫中列入考量。

相容性支援

基於回溯相容性，括號在 SELECT 陳述式中是選擇性。我們建議您永遠在 SELECT 陳述式中的 TOP 使用括號，以便與 INSERT、UPDATE、MERGE 和 DELETE 陳述式中的必要括號保持一致性。

互通性

TOP 運算式不會影響因觸發程序而執行的陳述式。在觸發程序中，inserted 和 deleted 資料表只會傳回真正受 INSERT、UPDATE、MERGE 或 DELETE 陳述式影響的資料列。例如，如果因使用 TOP 子句的 INSERT 陳述式而引發 INSERT TRIGGER，

SQL Server 允許透過檢視表更新資料列。由於 TOP 子句可包含在檢視表定義中，因此，如果資料列不再符合 TOP 運算式的需求，部分資料列可能會因更新而在檢視表中消失。

在 MERGE 陳述式中指定時，當整個來源資料表和整個目標資料表聯結在一起，而且不符合插入、更新或刪除動作的聯結資料列被移除之後，才會套用 TOP 子句。TOP 子句會進一步將聯結的資料列數減少為指定的值，而且插入、更新或刪除動作會依照未排序的方式套用到剩餘的聯結資料列。也就是說，將資料列散發到 WHEN 子句中定義的動作時，沒有任何特定順序。例如，如果指定 TOP (10) 會影響 10 個資料列，在這些資料列中，可能會更新 7 個及插入 3 個，或者可能會刪除 1 個、更新 5 個及插入 4 個，依此類推。因為 MERGE 陳述式會針對來源和目標資料表執行完整資料表掃描，所以當使用 TOP 子句，藉由建立多個批次來修改大型資料表時，I/O 效能可能會受到影響。在此狀況中，請務必確保所有後續批次都是以新的資料列為目標。

在查詢中指定包含了 UNION、UNION ALL、EXCEPT 或 INTERSECT 運算子的 TOP 子句時，請特別小心。您可能撰寫一個傳回非預期結果的查詢，因為當這些運算子用於選取作業時，TOP 和 ORDER BY 子句的邏輯處理順序不一定是直覺式。例如，提供下列資料表和資料時，假設您想要傳回最便宜的紅色汽車和最便宜的藍色汽車。也就是紅色轎車和藍色小貨車。

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');

若要達成這些結果，您可能撰寫下列查詢。

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;

以下為結果集：

Model         Color      Price

------------- ---------- -------

sedan         red        10000.00

convertible   blue       15000.00

傳回未預期的結果，因為 TOP 子句邏輯上在 ORDER BY 子句之前執行，這樣會排序運算子的結果 (在此例中為 UNION ALL)。因此，上一個查詢會傳回任何一輛紅色汽車和任何一輛藍色汽車，然後依價格來排序該聯集的結果。下列範例會顯示為了達到所要的結果，正確撰寫查詢的方法。

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;

藉由在子選擇作業中使用 TOP 和 ORDER BY，可以確保在套用至 TOP 子句時會使用 ORDER BY 子句的結果，而不會排序 UNION 作業的結果。

以下為結果集：

Model         Color      Price

------------- ---------- -------

sedan         red        10000.00

van           blue        8000.00

限制事項

搭配 INSERT、UPDATE、MERGE 或 DELETE 使用 TOP 時，不會以任何順序排列參考的資料列，也不可以直接在這些陳述式中指定 ORDER BY 子句。如果您需要使用 TOP 依有意義的時序來插入、刪除或修改資料列，TOP 必須與子選擇陳述式中指定的 ORDER BY 子句一起使用。請參閱本主題稍後的＜範例＞一節。

TOP 不能用於資料分割檢視上的 UPDATE 和 DELETE 陳述式。

TOP 無法與相同查詢運算式 (相同查詢範圍) 中的 OFFSET 和 FETCH 結合。如需詳細資訊，請參閱＜ORDER BY 子句 (Transact-SQL)＞。

範例

類別目錄

代表性語法元素

基本語法

TOP • PERCENT

包括相同值

WITH TIES

限制受 DELETE、INSERT 或 UPDATE 影響的資料列

DELETE • INSERT • UPDATE

基本語法

本節的範例會使用所需的最少語法來示範 ORDER BY 子句的基本功能。

A. 搭配常數值使用 TOP

下列範例使用常數值，來指定查詢結果集內傳回的員工人數。在第一個範例中，因為未使用 ORDER BY 子句，所以會傳回前 10 個未定義的資料列。在第二個範例中，ORDER BY 子句用來傳回前 10 名最近雇用的員工。

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;

B. 搭配變數使用 TOP

下列範例使用變數，來指定查詢結果集內傳回的員工人數。

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

C. 指定百分比

下列範例使用 PERCENT，來指定查詢結果集內傳回的員工人數。HumanResources.Employee 資料表有 290 名員工。因為 290 的 5% 是含小數的值，所以此值會無條件進位到下一個整數。

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

包括相同值

A. 使用 WITH TIES 包含符合最後一個資料列中之值的資料列

下列範例會取得所有員工中薪資最高的 10%，且會依照薪資的遞減順序傳回。指定 WITH TIES 可確定任何薪資是所傳回之最低薪資 (最後一個資料列) 的員工也會包含在結果集中，即使這麼做會超出員工的 10%，也是如此。

USE AdventureWorks2012;
GO
SELECT TOP(10)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;

限制受 DELETE、INSERT 或 UPDATE 影響的資料列

A. 使用 TOP 限制刪除的資料列數目

當 TOP (n) 子句與 DELETE 一起使用時，會以未定義的方式選取 n 個資料列來執行刪除作業。也就是說，DELETE 陳述式會選擇符合 WHERE 子句中所定義準則的任意 (n) 數目資料列。下列範例會從 PurchaseOrderDetail 資料表刪除到期日早於 2002 年 7 月 1 日的 20 個資料列。

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

如果您必須使用 TOP 依有意義的時序來刪除資料列，就必須在 subselect 陳述式中同時使用 TOP 和 ORDER BY。下列查詢會刪除 PurchaseOrderDetail 資料表中具有最早到期日的 10 個資料列。為確保只刪除 10 個資料列，subselect 陳述式 (PurchaseOrderID) 中指定的資料行是資料表的主索引鍵。如果指定的資料行包含重複值，則在 subselect 陳述式中使用非索引鍵資料行會造成刪除 10 個以上的資料列。

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

B. 使用 TOP 限制插入的資料列數目

下列範例會建立 EmployeeSales 資料表，而且會將 HumanResources.Employee 資料表的前 5 名員工的姓名和今年到目前的銷售資料插入其中。INSERT 陳述式會選擇 SELECT 所傳回符合 WHERE 子句中所定義準則的任 5 個資料列。OUTPUT 子句會顯示插入到 EmployeeSales 資料表的資料列。請注意，SELECT 陳述式中的 ORDER BY 子句不會用來判斷前 5 名員工。

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;

如果您必須使用 TOP 依有意義的時序來插入資料列，就必須搭配子選擇陳述式中指定的 ORDER BY 子句來使用 TOP，如下列範例所示。OUTPUT 子句會顯示插入到 EmployeeSales 資料表的資料列。請注意，現在插入的前 5 名員工是依據 ORDER BY 子句的結果，而不是未定義的資料列。

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;

C. 使用 TOP 限制更新的資料列數目

下列範例會利用 TOP 子句來更新資料表中的資料列。當 TOP (n) 子句與 UPDATE 一起使用時，更新作業會在未定義的資料列數目上執行。也就是說，UPDATE 陳述式會選擇符合 WHERE 子句中所定義準則的任意 (n) 數目資料列。下列範例會從某位銷售人員指派 10 位客戶給另一位銷售人員。

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

如果您必須使用 TOP 依有意義的時間順序套用更新，就要在子選擇陳述式中同時使用 TOP 與 ORDER BY。下例會更新最早雇用的前 10 名員工的休假時數。

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

請參閱

參考

SELECT (Transact-SQL)

INSERT (Transact-SQL)

UPDATE (Transact-SQL)

DELETE (Transact-SQL)

ORDER BY 子句 (Transact-SQL)

SET ROWCOUNT (Transact-SQL)

MERGE (Transact-SQL)