iT邦幫忙

2

[筆記][MSSQL]在2008版本中的分頁技巧

今天要介紹的是SQL的分頁技巧,首先先介紹在MSSQL2012版中新增的分頁語法,實務上主要應用在加載DataTable的時候,配合上頁數,讓網頁不會每一次都查詢全部的資料,導致效能變差。

以下使用北風資料庫的[Customers]做測試:
https://ithelp.ithome.com.tw/upload/images/20171118/20106935uN5iFMWQ2M.jpg

一、使用OFFSET ROWS

SELECT *
FROM Customers
--如果要使用分頁語法,一定要加上排序,不然會出錯。
ORDER BY CustomerID 
--這一行語法是指「略過」前5行,列出從第6行開始到最後一筆資料。
OFFSET 5 ROWS

結果會如下:
https://ithelp.ithome.com.tw/upload/images/20171119/20106935G9HA7gFv4L.jpg

Function:
1.OFFSET intA ROWS
此函數會略過前intA筆資料行,從第intA+1筆開始列到最後一筆。

二、使用OFFSET intA ROWS搭配FETCH NEXT intB ROWS ONLY

SELECT *
FROM Customers
--如果要使用分頁語法,一定要加上排序,不然會出錯。
ORDER BY CustomerID 
--這一行語法是指「略過」前2行,列出從第3行開始的8筆資料。
OFFSET 2 ROWS 
FETCH NEXT 8 ROWS ONLY

結果會如下:
https://ithelp.ithome.com.tw/upload/images/20171119/20106935rAT58OECZM.jpg

Function:
1.OFFSET intA ROWS FETCH NEXT intB ROWS ONLY
此函數會略過前intA筆資料行,從第intA+1筆開始列出intB筆資料。

以上分頁語法的使用方法,但是此語法是在2012版本中才出現的,如果是在2008等版本要使用分頁時,可以用以下方式:

三、使用ROW_NUMBER()搭配OVER(ORDER BY AAA)

SELECT *
FROM(
--先用ROW_NUMBER()替每筆資料設定編號
SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) rowNumber,*
FROM Customers) myTable
--再找出編號大於2,且小於等於2+8的資料
WHERE rowNumber>2 AND rowNumber<=2+8

結果會是和第二種方式一樣:
https://ithelp.ithome.com.tw/upload/images/20171119/20106935yHRSuXAk3v.jpg

說明一下這種方式的應用:
第一步用ROW_NUMBER()搭配OVER(ORDER BY AAA)替每筆資料設定編號。
第二步把他包成一個table,因為如果直接把ROW_NUMBER()產生的欄位放進WHERE裡面會出錯。
第三步再最外層使用WHERE,找出編號大於2,且小於等於2+8的資料行,這樣的結果就和從2到8行一樣。

以上,如果有問題或觀念錯誤的地方,麻煩留言指教了!謝謝各位!


2 則留言

0
fysh711426
iT邦新手 3 級 ‧ 2017-11-19 02:35:54

FETCH 寫法效能比 ROW_NUMBER() 快很多。
/images/emoticon/emoticon35.gif

哈哈,對哇!
只是前幾天要配合客戶的舊系統使用2008版本的SQLSERVER 就只能這樣惹,嗚嗚。

0
aptx1596
iT邦新手 4 級 ‧ 2017-11-22 20:13:02

這兩年微軟真的變強很多呢

我要留言

立即登入留言