筆記-T-SQL 分頁查詢並傳回總筆數
7 |
資料庫查詢結果要做分頁,我較常用的做法是拉到 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.
# by 小黑
感謝黑哥分享,但心中有個疑問請教 一般這部分實務上會做成 Stored Proceduce 並提供pageNo及pageSize 參數傳入呼叫?若需要滿足使用者動態搜尋條件組合又該如何設計?
# by Jeffrey
to 小黑,在我的認知,是否該一切回歸 Stored Procedure 在實務上看法是分歧的,隨著 LINQ/EF 概念興盛,若要堅守這種信念大概就跟這些新技術絕縁了。因此,我不是很贊同「實務上都會做成 SP」的說法,但回到你的疑問,若要堅持非 SP 不可,較嚴謹的做法是事先分析好可能的動態條件,以參數方式傳入查詢條件,在 SP 中寫邏輯組裝查詢條件。呼叫端組成 SQL 字串交給 SP 執行可行,但很容易帶來 SQL Injection 風險,不建議採用。
# by 小黑
謝謝黑哥
# by Ellis
感謝您的教學(受益許多)。