[MSSQL] 使用 T-SQL 實作分頁

很多表格會遇上資料有上萬筆資料或者上千萬時的大數據,這時如果還是把所有資料撈出來在分頁的話,會造成使用者頁面讀取時間過久,更會有伺服器壓力倍增的問題,這裡提供一個SQL解決方案。

SQL如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

DECLARE @PageNum AS BIGINT;
DECLARE @PageSize AS BIGINT;
DECLARE @PageCount AS BIGINT;
SET @PageNum = 3;
SET @PageSize = 10;
SET @PageCount = ( SELECT COUNT(1)
FROM dbo.TBase_Product
WHERE Product_State != 9) / @PageSize;

IF @PageNum < 1 SET @PageNum = 1;

WITH Temp AS
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY Product_ID) AS RowNum
FROM dbo.TBase_Product
)

SELECT RowNum, @PageCount AS PageCount
FROM Temp
WHERE RowNum
BETWEEN ((@PageNum - 1) * @PageSize + 1)
AND (@PageNum * @PageSize)
ORDER BY Product_ID;

執行結果:

2016-09-03 01_01_10-SQLQuery9.sql - exfast.me,6000.iPCE_Coding (shuangrain (143))_ - Microsoft SQL S

 

 

參考:

  1. How to return a page of results from SQL?
  2. Select Count(*) / Count(1) / Count(欄位名) 的差異