MS SQL 分頁+排序語法

參考
--
MS-SQL 2000以前，常見的分頁用語法。
SELECT * FROM
(
SELECT TOP [每頁長度] * FROM
(
SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...]
FROM [資料表]
WHERE [篩選條件]
ORDER BY [排序欄位] [DESC/ASC]
)
AS [資料表别名1]
ORDER BY [排序欄位] [ASC/DESC]
)
AS [資料表别名2]
ORDER BY [排序欄位] [DESC/ASC]MS-SQL Type C


***************************************************************************************************************
MS-SQL 2005以後才有RANK()語法。
SELECT * FROM
(
SELECT rank() OVER ( ORDER BY [排序欄位] [ASC/DESC] ) AS RankNumber, * FROM
(
SELECT [欄位1, 欄位2, ...]
FROM [資料表]
WHERE [篩選條件]
)
AS [資料表别名1]
) AS [資料表别名2]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]MS-SQL Type D


***************************************************************************************************************
MS-SQL 2000可用，但Select時，不可包含PK用的欄位。（需透過暫存資料表操作）
參考資料：(SQL)抽獎，亂數抽出10筆中獎資料並排名
SELECT IDENTITY(INT,1,1) AS RankNumber, [欄位1, 欄位2, ...]
INTO [#暫存資料表名稱]
FROM [資料表]
ORDER BY [排序欄位] [ASC/DESC];

SELECT * FROM [#暫存資料表名稱]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁];

DROP TABLE [#暫存資料表名稱];MySQL
SELECT [欄位1, 欄位2, ...]
FROM [資料表]
WHERE [篩選條件]
ORDER BY [排序欄位] [ASC/DESC]
LIMIT [每頁長度] OFFSET [每頁長度*第幾頁]