筆記-T-SQL 分頁查詢並傳回總筆數

資料庫查詢結果要做分頁,我較常用的做法是拉到 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 版本相同:

歡迎推文分享:
Published 23 August 2017 09:33 PM 由 Jeffrey
Filed under: ,
Views: 5,434



意見

# Ken said on 23 August, 2017 10:43 AM

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;

# Jeffrey said on 23 August, 2017 08:21 PM

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.

# Ken said on 24 August, 2017 04:56 AM

ahh, good point, thanks.

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<August 2017>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication