歡迎~ 程式設計 學生 作業 專題製作 程式外包 家教...

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 [每頁長度*第幾頁]