OVER 子句 (Transact-SQL)
更新： 2006 年 7 月 17 日
在套用相關的視窗函數之前，決定資料列集的資料分割和排序。
適用於：
次序視窗函數
彙總視窗函數。如需詳細資訊，請參閱＜彙總函數 (Transact-SQL)＞。
.gif "主題連結圖示")
語法
Ranking Window Functions
<OVER_CLAUSE> :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
<ORDER BY_Clause> )
Aggregate Window Functions
<OVER_CLAUSE> :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
引數
- PARTITION BY
將結果集分成幾個資料分割。視窗函數會分別套用至每個資料分割，並且針對每個資料分割重新開始計算。
- value_expression
指定資料行，該資料行是分割由對應 FROM 子句所產生之資料列集所依據的資料行。<value_expression> 只能指定 FROM 子句所能使用的資料行，它無法參考選取清單中的運算式或別名。<value_expression> 可以是資料行運算式、純量子查詢、純量函數或使用者自訂變數。
<ORDER BY 子句>
指定套用次序視窗函數的順序。如需詳細資訊，請參閱＜ORDER BY 子句 (Transact-SQL)＞。.gif "ms189461.note(zh-tw,SQL.90).gif")
重要事項：<ORDER BY 子句> 如果用在次序視窗函數的內容中，只能指定 FROM 子句所能使用的資料行，不能指定整數來代表選取清單中的名稱位置或資料行別名。<ORDER BY 子句> 不能搭配彙總視窗函數使用。
備註
視窗函數定義於 ISO SQL 標準中。SQL Server 會提供次序和彙總視窗函數。視窗是指使用者指定的資料列集。視窗函數會針對衍生自視窗之結果集中的每個資料列，計算值。
含有一個 FROM 子句的一個查詢中，可以使用多個次序或彙總視窗函數。不過，每個函數的 OVER 子句在進行資料分割和進行排序時，都不一樣。OVER 子句不能搭配 CHECKSUM 彙總函數使用。
範例
A. 搭配 ROW_NUMBER 函數來使用 OVER 子句
每個次序函數 (ROW_NUMBER、DENSE_RANK、RANK、NTILE) 都會使用 OVER 子句。下列範例顯示搭配 ROW_NUMBER 使用 OVER 子句。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
B. 搭配彙總函數來使用 OVER 子句
下列範例顯示搭配彙總函數來使用 OVER 子句。在這個範例中，使用 OVER 子句比使用子查詢更有效率。
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO
以下為結果集：
| SalesOrderID | ProductID | OrderQty | Total | Avg | Count | Min | Max |
|---|---|---|---|---|---|---|---|
43659 |
776 |
1 |
26 |
2 |
12 |
1 |
6 |
43659 |
777 |
3 |
26 |
2 |
12 |
1 |
6 |
43659 |
778 |
1 |
26 |
2 |
12 |
1 |
6 |
43659 |
771 |
1 |
26 |
2 |
12 |
1 |
6 |
43659 |
772 |
1 |
26 |
2 |
12 |
1 |
6 |
43659 |
773 |
2 |
26 |
2 |
12 |
1 |
6 |
43659 |
774 |
1 |
26 |
2 |
12 |
1 |
6 |
43659 |
714 |
3 |
26 |
2 |
12 |
1 |
6 |
43659 |
716 |
1 |
26 |
2 |
12 |
1 |
6 |
43659 |
709 |
6 |
26 |
2 |
12 |
1 |
6 |
43659 |
712 |
2 |
26 |
2 |
12 |
1 |
6 |
43659 |
711 |
4 |
26 |
2 |
12 |
1 |
6 |
43664 |
772 |
1 |
14 |
1 |
8 |
1 |
4 |
43664 |
775 |
4 |
14 |
1 |
8 |
1 |
4 |
43664 |
714 |
1 |
14 |
1 |
8 |
1 |
4 |
43664 |
716 |
1 |
14 |
1 |
8 |
1 |
4 |
43664 |
777 |
2 |
14 |
1 |
8 |
1 |
4 |
43664 |
771 |
3 |
14 |
1 |
8 |
1 |
4 |
43664 |
773 |
1 |
14 |
1 |
8 |
1 |
4 |
43664 |
778 |
1 |
14 |
1 |
8 |
1 |
4 |
下列範例顯示在導出值中搭配彙總函數來使用 OVER 子句。
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
*100 AS DECIMAL(5,2))AS 'Percent by ProductID'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO
以下為結果集： 請注意，彙總是以 SalesOrderID 來計算，而且每個 SalesOrderID 的每一行都會計算出 Percent by ProductID。
| SalesOrderID | ProductID | OrderQty | Total | Percent by ProductID |
|---|---|---|---|---|
43659 |
776 |
1 |
26 |
3.85 |
43659 |
777 |
3 |
26 |
11.54 |
43659 |
778 |
1 |
26 |
3.85 |
43659 |
771 |
1 |
26 |
3.85 |
43659 |
772 |
1 |
26 |
3.85 |
43659 |
773 |
2 |
26 |
7.69 |
43659 |
774 |
1 |
26 |
3.85 |
43659 |
714 |
3 |
26 |
11.54 |
43659 |
716 |
1 |
26 |
3.85 |
43659 |
709 |
6 |
26 |
23.08 |
43659 |
712 |
2 |
26 |
7.69 |
43659 |
711 |
4 |
26 |
15.38 |
43664 |
772 |
1 |
14 |
7.14 |
43664 |
775 |
4 |
14 |
28.57 |
43664 |
714 |
1 |
14 |
7.14 |
43664 |
716 |
1 |
14 |
7.14 |
43664 |
777 |
2 |
14 |
14.29 |
43664 |
771 |
3 |
14 |
21.43 |
43664 |
773 |
1 |
14 |
7.14 |
43664 |
778 |
1 |
14 |
7.14 |
請參閱
參考
次序函數 (Transact-SQL)
彙總函數 (Transact-SQL)
說明及資訊
變更歷程記錄
| 版本 | 歷程記錄 |
|---|---|
2006 年 7 月 17 日 |
|