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

goodlucky 發表在 痞客邦 PIXNET 留言(0) 人氣()