SQL 2012新功能 OFFSET-FETCH－Louis學習筆記

什麼是OFFSET-FETCH？

簡單來說就是"分頁處理"功能

那什麼是"分頁處理"？而"分頁處理"可以運用在哪邊？跟過往處理有什麼差別？

分頁處理就是將查詢出來的資料集合加以分頁，並抽取當中的特定分頁資料。

舉例學生成績為例，假設一個年級有100名學生，我們想要根據學生考試的總成績進行排序

再以10名為一個區隔（分頁），將學生分成10等分。

老師可能會想要抽出指定的一個區隔內的資料出來查看是那些學生。

過往資訊人員在處理時一般有兩種做法，

將100筆資料全都抓進ap端，再透過一些網頁技巧做到分頁效果

問題是100筆資料還好，當資料筆數越多時，取出過多不一定會用到的資料存DB端傳輸到AP端

會造成資源浪費以及佔據有效的頻寬，這種做法比較適合數據量較小的案例

將100筆資料額外再添加欄位儲存排名序號，再取出指定序號的資料出來

這種作法改善了過多資料傳輸的問題，但相對的需要額外增加資料欄位或是使用更多的語法

可能會增加資料庫運算的負擔以及語法撰寫的難度

因此，SQL Server在2012版本出來時，終於提供了分頁功能來處理這方面的問題

http://technet.microsoft.com/zh-tw/library/gg699618(v=sql.110).aspx

語法其實滿簡潔乾淨的

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

SELECT [資料欄位]

FROM [資料表]

ORDER BY [資料欄位]

OFFSET N ROWS(ROW)

FETCH NEXT(FIRST) M ROWS(ROW) ONLY

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

在原本的語法後面(要有ORDER BY子句)，加上OFFSET以及FETCH兩段(藍色為主要語法，紅色為可替換但沒差別)

OFFSET N ROWS：代表略過Ｎ筆開始（可以單獨使用不用加上FETCH子句）

FETCH NEXT M ROWS ONLY：代表取接下來Ｍ筆資料（一定要配合OFFSET子句使用)

範例

以學生考試成績為例

先隨機建立一百筆學生三科的成績

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

IF OBJECT_ID('Score') is null
BEGIN
CREATE TABLE Score
(
　　StudentID int primary key,
　　E_Score int,
　　C_Score int,
　　M_Score int
)
DECLARE @CNT INT
SET @CNT=1
WHILE @CNT<=100
BEGIN
　　INSERT INTO Score(StudentID,E_Score,C_Score,M_Score)
　　VALUES (@CNT,CONVERT(int,40*RAND()+60),CONVERT(int,40*RAND()+60),CONVERT(int,40*RAND()+60))
SET @CNT=@CNT+1
END
END

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

舉例

--根據三科總分進行排序，由大至小(DESC)
SELECT StudentID,E_Score,C_Score,M_Score,E_Score+C_Score+M_Score as Total
FROM Score
ORDER BY Total DESC
--跳過前10筆取後面的資料
SELECT StudentID,E_Score,C_Score,M_Score,E_Score+C_Score+M_Score as Total
FROM Score
ORDER BY Total DESC
OFFSET 10 ROWS
--跳過前10筆取接續10筆的資料
SELECT StudentID,E_Score,C_Score,M_Score,E_Score+C_Score+M_Score as Total
FROM Score
ORDER BY Total DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY