引用來源: [MSSQL]分頁
---
方法一 : 使用ROW_NUMBER()搭配OVER(ORDER BY Field)(SQL Server 2008 開始)
ROW_NUMBER()
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
先用ROW_NUMBER()替每筆資料設定編號，再根據條件取得分頁內容(ROW_NUMBER從1開始)
DECLARE @PageIndex INT = 1, @PageSize INT = 25
SELECT * FROM
(
SELECT
*, ROW_NUMBER() OVER (ORDER BY FieldA DESC) as RowId
FROM
TableA
WHERE
Conditions
) AS vw
WHERE
RowId BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
方法二 : 使用OFFSET搭配FETCH(SQL Server 2012 開始)
OFFSET FETCH
OFFSET - FETCH 是 ORDER BY 子句的延伸功能。
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
可以指定跳過的行數，指定要取回的資料列筆數(要從跳過 0 ROW開始)
DECLARE @PageIndex INT = 1, @PageSize INT = 25
SELECT
*
FROM
TableA
ORDER BY
FieldA DESC
OFFSET (@PageIndex - 1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
FETCH 寫法效能比 ROW_NUMBER() 快很多
