[MSSQL] 4種SQL分頁方式

2011-04-10

13643
0
MSSQL
2011-04-13

摘要:[MSSQL] 4種SQL分頁方式

MS-SQL Type A
無排序，在關聯式查詢時，可能導致每次查詢同一分頁的資料時，出來的資料都不一樣。


SELECT [欄位1, 欄位2, ...] FROM [資料表]
    WHERE [主要索引欄位] IN
    (
        SELECT * FROM 
        (
            SELECT TOP [每頁長度*第幾頁] [主要索引欄位]
                FROM [資料表]
                WHERE [篩選條件]     
        )
        AS [資料表别名1]
        WHERE [主要索引欄位] NOT IN
        (
            SELECT TOP [每頁長度] [主要索引欄位] 
                FROM [資料表]
                WHERE [篩選條件] 
        )
    )

MS-SQL Type B
有排序，又稱【夾擠式分頁查詢】。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用的欄位。（需透過暫存資料表操作）


SELECT IDENTITY(INT,1,1) AS RankNumber, [欄位1, 欄位2, ...] 
INTO [#暫存資料表名稱] 
FROM [資料表]
ORDER BY [排序欄位] [ASC/DESC];
 
SELECT * FROM [#暫存資料表名稱]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁];
 
DROP TABLE [#暫存資料表名稱];

引用來源：http://www.player.idv.tw/prog/index.php?title=SQL#MS-SQL

回首頁