iT邦幫忙

2

[筆記][MSSQL]關於OVER子句的使用方式

昨天打完文章在逛技術問答區的時候發現一個問題:
https://ithelp.ithome.com.tw/questions/10187148
這是有關排序的一個問題,而下面也有人回答說可以把欄位內容用SUBSTRING分成兩個字串去做排序解決,但是讓我注意到的是下方「純真的人」的留言還有「一級屠豬士」的回答,他們都使用了OVER子句來解決這個問題,因為之前很少看過這個子句,而且看官方文件使用OVER子句會比子查詢還要來的有效率,所以去找了相關資料來整理,其實官方的文件已經寫得很清楚了,不過還是列出常見的使用方法。

以下使用北風資料庫的[Order Details]做測試:
https://ithelp.ithome.com.tw/upload/images/20171011/201069357I4nRaMCDl.jpg

一、搭配ROW_NUMBER()


SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) rowNumber,
OrderID,ProductID
FROM [Order Details]

結果會如下:
https://ithelp.ithome.com.tw/upload/images/20171011/20106935J7G6gayHJ6.jpg
上圖的結果為以每個訂單編號做分割,再在裡面對每個產品下編號,到了不同的訂單編號在從1開始編號。

Function:
1.ROW_NUMBER() OVER(PARTITION BY AAA ORDER BY BBB)
OVER子句內的PARTITION BY可以指定AAA欄位做分割,被分割的會自成一個群組,並以BBB欄位下去排序編號。

二、搭配函式做使用,此範例以加總函式SUM()來試試


SELECT OrderID,ProductID,
SUM(UnitPrice*Quantity) OVER(PARTITION BY OrderID) total 
FROM [Order Details]

結果會如下:
https://ithelp.ithome.com.tw/upload/images/20171011/20106935gP0oNeDz1A.jpg
上圖的結果為把每張訂單的總合算出來,如此一來就不用在經過子查詢,也可以SELECT出總計。

Function:
1.SUM(BBB) OVER(PARTITION BY AAA)
OVER子句內的PARTITION BY可以指定AAA欄位做分割,被分割的會自成一個群組,並將該群組的BBB欄位帶進函式計算。

參考文章:
1.https://docs.microsoft.com/zh-tw/sql/t-sql/queries/select-over-clause-transact-sql


尚未有邦友留言

立即登入留言