[ADO.NET]分頁查詢

這篇是回應微軟 MSDN 論壇討論區:
原發問者javavbs2006的問題:


資料庫載入資料問題
 

請教載入資料要控制第幾筆載入到第幾筆,該怎麼寫
第1到10筆資料
Select top 10 * From ….
第10到20筆資料
Select XXXX Form…
XXX要怎麼寫,還是要用其他的語法呢?謝謝。


我只摘錄我的回覆,其他人參與討論過請瀏覽該篇討論。

先是有位網友提到 ROW_NUMBER ,這個方法德瑞克也跟我推薦過,不過欠缺補充說明,所以我追加了一段:


ROW_NUMBER 是 SQL Server 的特殊解,並不適用於所有資料庫查詢語言,做範例介紹時,建議補充說明。

使用 ROW_NUMBER 這類方法會比在 Fill 指定範圍要好,因為傳回來的資料總數本身減少了,不像 Fill 是所有資料傳回來,在填入指定的資料。這部份討論先前有討論過,忘了是舊站還是新站這邊。

 

這篇官方文件有關於分頁的建議: 

使用 ADO.NET 的最佳經驗分享

http://www.microsoft.com/taiwan/msdn/library/2003/Jul-2003-tw/adonetbest.htm

分頁

ADO.NET 提供您在資料從資料來源傳回時額外的控制能力,以及要將多少資料快取在本地的 DataSet中。要對查詢的結果進行分頁的作業方式並不是唯一的,旦是在設計您的應用程式時有一些秘訣可以考慮。

  • 避免使用 DataAdapter.Fill 中採用 startRecordmaxRecords 的值做為引數的多載型式。當以這種方式填入 DataSet 時,DataSet 只會填入 maxRecords 參數中所指定個數的紀錄(由 startRecord 參數所指定的紀錄開始),但是傳回的還是整個查詢的結果。結果是花費資源處理並不需要的紀錄,同時也耗用不必要的伺服器資源來傳回額外的紀錄。
  • 一次只有傳回一頁紀錄的技巧之一是建立一個組合了 WHERE 子句及一個 ORDER BY 子句的 SQL 敘述,同時搭配 TOP 述語。這個技巧要依賴能夠辨識出每一個個別的資料列。當巡覽到下一頁的紀錄時,就修改 WHERE 子句包含所有唯一的辨識代碼大於目前頁面最後一個辨識代碼的紀錄。當巡覽到前一頁的紀錄時,則是修改 WHERE 子句傳回所有唯一辨識代碼小於目前頁面第一筆唯一辨識代碼的紀錄。針對這兩個查詢,都只傳回前幾筆由 TOP 述語所指定的紀錄。當巡覽到前一頁時,您需要將紀錄以遞減的方式排列。這樣就會有效的傳回該查詢的底頁紀錄 (而在顯示之前可能還要再重新對取回的結果進行排序)。有關這個技巧的範例,可以參考 Paging Through a Query Result 一文。
  • 另一個一次只傳回一頁紀錄的技巧是建立一個組合了 TOP 述語及內嵌 SELECT 敘述的 SQL 敘述。這個技巧並不需要依賴辨識唯一一筆紀錄的方法。這個技巧的第一筆是將頁面的大小與所要顯示的頁面數相乘,然後將這個數字傳入到 SQL 敘述的 TOP 述語中,以遞增的方式排列。然後將這個查詢嵌在另一個查詢中,只從內嵌的查詢裡挑出 TOP 述語所指定的一頁紀錄數,並以遞減的方式排列。這樣,就會傳回內嵌查詢中底頁的資料頁。例如,要傳回第三頁的查詢結果,每一頁是 10 筆紀錄,您可能會執行以下的命令:
    SELECT TOP 10 * FROM
      (SELECT TOP 30 * FROM Customers ORDER BY Id ASC) AS Table1
    ORDER BY Id DESC

    注意到這個查詢的結果會是以遞減的方式排列,您可以自行再改變排列的順序。

  • 如果您的資料不常改變,您可以藉由一個 DataSet 物件在本地維護紀錄的快取。例如,您可以儲存 10 頁的資料在本地的 DataSet 中,並且只有在使用者巡覽到超出快取中第一頁和最後一頁的資料時,才去查詢資料來源取得新的資料。

相關的資訊請參考 .NET Data Access Architecture Guide

前面所說的懶人方法:「採用 startRecordmaxRecords 的值做為引數的多載型式」請參考線上手冊:

http://msdn2.microsoft.com/zh-tw/library/59wzthcw(VS.80).aspx


之後原發問者參考上述諸多網友的意見後,提出三種方法:


謝謝大家的指點,我終於會了..^^

一次載入20筆資料

●法一:

第一頁
SelectCmd = "select top 20 * from GameDB Order By  GameSn ASC"

第二頁
pageSize = 20
SelectCmd = "select top 20 * from GameDB where GameSn not in(select top " & pageSize & " GameSn from GameDB Order By GameSn ASC) Order By  GameSn ASC"

第三頁
pageSize = 40
SelectCmd = "select top 20 * from GameDB where GameSn not in(select top " & pageSize & " GameSn from GameDB Order By GameSn ASC) Order By  GameSn ASC"

●法二:

第一頁
pageSize = 20
SelectCmd = "select top 20 * from (select top " & pageSize & " * from GameDB Order By GameSn ASC) Order By GameSn DESC"

第二頁
pageSize = 40
SelectCmd = "select top 20 * from (select top " & pageSize & " * from GameDB Order By GameSn ASC) Order By GameSn DESC"

●法三:

第一頁
pageSize = 20
lastRecord = 0
SelectCmd = "SELECT TOP " & pageSize & " * FROM GameDB WHERE GameSn > " & lastRecord & " ORDER BY GameSn"

第二頁
pageSize = 20
lastRecord = 20
SelectCmd = "SELECT TOP " & pageSize & " * FROM GameDB WHERE GameSn > " & lastRecord & " ORDER BY GameSn"


我再補充說明如下:

通常是用法二,而且不是你這樣用。見微軟建議的語法是法二。 

先說原因:

法 1: 用 not In ,這個要作邏輯判斷,效能差也就算了,要是在分頁時剛好有相同資料,就會被排除。

法 2: 少了一次 Select,會反序,要嘛次序相反,要嘛頁數相反。

法 3: 同前,要是 GameSN 在跨頁時剛好號碼相同… 此外,多欄位的排序或條件,法 3 無法處理。

 

所以:

1. 若沒有排序要求,就只能靠 Fill(…, StartRecord, MaxRecord) 來處理。

2.

a.有排序要求,不論幾個欄位,從語法分析抽出來後,稱為正排,再依據語法分析反轉排序稱為反排 (例如:正排 Order By Field1, Field2 => 反排 Order By Field1 DESC, Field2 DESC)

b.原查詢 Select 替代為 Select Top PageNumber * PageSize (PageNumber 為頁數,PageSize 為每頁的筆數)

SQL 語法

Select * From (
   Select Top PageSize * From (
      Select Top PageNumber * PageSize + 原句欄位、表格、條件 + 正排
      ) 反排
   ) 正排 

註:部分資料庫不支援 Top ,例如 MySQL ,須參考該資料庫手冊來調整語法,此語法可在 Access / SQL Server 等微軟產品下使用。

廣告
Categories: 資料庫 | 發表留言

文章分頁導航

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s

在WordPress.com寫網誌.

%d 位部落客按了讚: