SQL 分頁語法
Web上最重要的就是速度跟資源
資料的傳輸量越小 速度就越快
因此在Windows Form上一次把所有資料篩選出來的做法
到了Web上就變得很可怕
或許在其他的功能上為了方便 無法捨棄便利的物件
那麼至少在資料的篩選方面下點功夫
由於目前的專案會用到大量的資料庫資料
此外還有圖片~等
因此如果使用GrideView這樣的元件內建的分頁
到時候效能可能會讓我被罵到臭頭
再者 由於我使用的元件並不是GridView...而是沒有"內建"分頁功能的DataList
因此使用SQL語法進行分頁的查詢變成了我得要學習的課題
其實說來也不難
者是一小段SQL語法 還是讓我足足摸了一個禮拜(包括偷懶.打屁.哈拉.被其他網路資訊誤導..)
因此就在這裡記錄下來
使用下列語法可以篩選出我要的 每頁 顯示資料筆數 假設為6 筆資料
SELECT TOP 6 * FROM TableA
top n 的語法 用來取出 最前面n筆資料
如果加上Order by 就可以先做好我們要的排序 在取出排序完最前面n筆的資料
SELECT TOP 6 * FROM TableA Order by TableA.ID
現在我們要加上分頁查詢的功能 使用NOT IN 語法 配合子查詢
SELECT TOP 6 * FROM TableA
WHERE TableA.ID
NOT IN ( SELECT TOP ( 6 * pageIndex ) * FROM TableB.ID order by B.ID )
Order by A.ID
上面的語法有幾點要注意
第一當然就是頁面要顯示的筆數 我們設定為 6 筆 當然可以用變數 替代
但是 子查詢中的 筆數也要 一樣
子查詢中的 6 * pageIndex , pageIndex 指的是 要顯示哪一頁的頁碼 – 1
第一頁 就是 1-1 也就是0 第二頁 是2-1 也就是1
這樣配合起來才能做到排除查詢
另外Order By的 排序必須一致
當然如果有其他Where條件 也必須一致
NOT IN 則是 主查詢 的資料 必須排除 子查詢中的資料 當然包含限制條件
有的網頁資料寫必須相反
是錯誤的 如果在紙上作業一下 就知道 邏輯上有錯誤
另外通常 這樣的做做法都會配合遞迴查詢
也就是TableA 與 TableB 是同一個表 我再這裡 用不同名稱表示 比較好理解
遞迴查詢可以用別名來 識別 子查詢 與外圈的查詢
不過 原則上子查詢 與 外面的查詢 各成區塊 並不會 有名稱模糊的問題 倒也不一定要用別名
給個完整了範例
假設有個Table 叫做 FileInfo 有個欄位叫 ID 是索引值
有個state欄位 表示該紀錄是否有效 Y 為有效 其他值則為無效
裡面有15筆資料 我們要每頁 4 筆的查詢 排序要以ID降冪排序 只查詢有效資料
SELECT TOP @Rows * FROM FileInfo
WHERE state=’Y’ AND FileInfo.ID NOT IN ( SELECT TOP (@Rows * @PIndex) * FROM FileInfo Where state=’Y’ Order by ID DESC )
Order by ID DESC
Rows參數給 4
要查詢第1頁 PIndex 參數給0
第2頁 PIndex 參數給1…類推
Web上最重要的就是速度跟資源
資料的傳輸量越小 速度就越快
因此在Windows Form上一次把所有資料篩選出來的做法
到了Web上就變得很可怕
或許在其他的功能上為了方便 無法捨棄便利的物件
那麼至少在資料的篩選方面下點功夫
由於目前的專案會用到大量的資料庫資料
此外還有圖片~等
因此如果使用GrideView這樣的元件內建的分頁
到時候效能可能會讓我被罵到臭頭
再者 由於我使用的元件並不是GridView...而是沒有"內建"分頁功能的DataList
因此使用SQL語法進行分頁的查詢變成了我得要學習的課題
其實說來也不難
者是一小段SQL語法 還是讓我足足摸了一個禮拜(包括偷懶.打屁.哈拉.被其他網路資訊誤導..)
因此就在這裡記錄下來
使用下列語法可以篩選出我要的 每頁 顯示資料筆數 假設為6 筆資料
SELECT TOP 6 * FROM TableA
top n 的語法 用來取出 最前面n筆資料
如果加上Order by 就可以先做好我們要的排序 在取出排序完最前面n筆的資料
SELECT TOP 6 * FROM TableA Order by TableA.ID
現在我們要加上分頁查詢的功能 使用NOT IN 語法 配合子查詢
SELECT TOP 6 * FROM TableA
WHERE TableA.ID
NOT IN ( SELECT TOP ( 6 * pageIndex ) * FROM TableB.ID order by B.ID )
Order by A.ID
上面的語法有幾點要注意
第一當然就是頁面要顯示的筆數 我們設定為 6 筆 當然可以用變數 替代
但是 子查詢中的 筆數也要 一樣
子查詢中的 6 * pageIndex , pageIndex 指的是 要顯示哪一頁的頁碼 – 1
第一頁 就是 1-1 也就是0 第二頁 是2-1 也就是1
這樣配合起來才能做到排除查詢
另外Order By的 排序必須一致
當然如果有其他Where條件 也必須一致
NOT IN 則是 主查詢 的資料 必須排除 子查詢中的資料 當然包含限制條件
有的網頁資料寫必須相反
是錯誤的 如果在紙上作業一下 就知道 邏輯上有錯誤
另外通常 這樣的做做法都會配合遞迴查詢
也就是TableA 與 TableB 是同一個表 我再這裡 用不同名稱表示 比較好理解
遞迴查詢可以用別名來 識別 子查詢 與外圈的查詢
不過 原則上子查詢 與 外面的查詢 各成區塊 並不會 有名稱模糊的問題 倒也不一定要用別名
給個完整了範例
假設有個Table 叫做 FileInfo 有個欄位叫 ID 是索引值
有個state欄位 表示該紀錄是否有效 Y 為有效 其他值則為無效
裡面有15筆資料 我們要每頁 4 筆的查詢 排序要以ID降冪排序 只查詢有效資料
SELECT TOP @Rows * FROM FileInfo
WHERE state=’Y’ AND FileInfo.ID NOT IN ( SELECT TOP (@Rows * @PIndex) * FROM FileInfo Where state=’Y’ Order by ID DESC )
Order by ID DESC
Rows參數給 4
要查詢第1頁 PIndex 參數給0
第2頁 PIndex 參數給1…類推
