資料庫查詢結果要做分頁,我較常用的做法是拉到 C# 端將物件陣列或 List<T> 存入 MemoryCache,用 .Length 可以取總筆數,用 Skip(pageSize  * (pageNo - 1)).Take(pageSize) 取回指定頁數資料,換頁或排序時從 MemoryCache 讀取以求迅速並減輕資料庫伺服器負擔,遇到變更查詢條件或按查詢鈕時再重新查詢資料庫。

最近遇到的案例,因使用者較多、單筆資料量也大,擔心 Cache 消耗過多記憶體,決定改用 T-SQL 實做分頁同時取得總筆數,過去少有機會練習,試作之餘寫篇筆記備忘。

我找到較簡潔的做法是組裝查詢條件先轉成 CTE,用 Count(1) OVER () 計算總筆數放在每筆資料第一欄(重複資料會浪費空間,但既然會做分頁筆數不會太多,耗損可忽略),再使用 OFFSET + FETCH NEXT 子句實現類似 LINQ Skip() 與 Take() 的效果,程式範例如下:參考來源

DECLARE @pageSize INT, @pageNo INT;
SET @pageSize = 25;
SET @pageNo = 3;
;WITH T
AS (
    SELECT *
    FROM Production.Product
    WHERE ListPrice > 10
    )
SELECT TotalCount = COUNT(1) OVER (), T.*
FROM T
ORDER BY ProductNumber OFFSET(@pageNo - 1) * @pageSize ROWS
 
FETCH NEXT @pageSize ROWS ONLY;

2017-08-24 補充:回應網友 Ken 提問:CTE 並非絕對必要,寫成 CTE 的好處是形成標準範本,應用於不同場合時只需置換 CTE 內的查詢語法,其他部分不動,更容易寫成共用程式模組。

拿 AdventureWorks 資料庫 Production.Product 練兵,執行可得總筆數 291 筆,每頁 25 筆取第 3 頁,查得 25 筆:

關於 OFFSET 與 FETCH 的詳細介紹,可參考 德瑞克:SQL Server 學習筆記- SQL Server 2012 :分頁處理:認識 OFFSET 和 FETCH 子句

OFFSET 跟 FETCH 是 SQL 2012 才加入的新指令,如果你的資料庫還停在滿清時代 SQL 2005 或 SQL 2008,就只能回歸使用 ROW_NUMBER() 配合 BETWEEN 分頁,但配合 CTE 使用,查詢稍稍複雜,違和程度尚在可忍受範圍。 (但如果不用 CTE,而是同樣查詢條件 Copy and Paste 就會讓人想吐了…)程式範圍如下:參考來源

DECLARE @pageSize INT, @pageNo INT;
SET @pageSize = 25;
SET @pageNo = 3;
;WITH T
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ProductNumber) AS RowNo,*
    FROM Production.Product
    WHERE ListPrice > 10
    ),
T2 AS (
    SELECT COUNT(1) TotalCount FROM T
)
SELECT *
FROM T2, T
WHERE RowNo BETWEEN (@pageNo - 1) * @pageSize  + 1 
AND @pageNo * @pageSize;

執行結果與 OFFSET + FETCH 版本相同:


Comments

# by Ken

Sorry to ask - why do you need to use CTE? or what is the benefit of using it in this particular case? I thought it is possible to do without it, and the code looked simpler too? DECLARE @pageSize INT, @pageNo INT; SET @pageSize = 25; SET @pageNo = 3; SELECT TotalCount = COUNT(1) OVER (), * FROM Production.Product WHERE ListPrice > 10 ORDER BY ProductNumber OFFSET(@pageNo - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;

# by Jeffrey

to Ken, Yes, CTE is not necessary. Packing the major query into CTE makes it a standard programming pattern, you can replace the query inside CTE with any query and keeping remaining part fixed, it will be easier to use in different scenarios. Thanks for your feedback.

# by Ken

ahh, good point, thanks.

Post a comment


78 + 19 =