Sql Server系列：查詢分頁語句

libingql發表於2015-05-02
SQLServer

1 利用臨時表分頁

　　分頁儲存過程：

CREATE PROCEDURE [USP_Product_GetPaged]
    @WhereClause VARCHAR (2000),
    @OrderBy VARCHAR (2000),
    @PageIndex INT,
    @PageSize INT
AS
    BEGIN
        DECLARE @PageLowerBound INT, @PageUpperBound INT

        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageLowerBound + @PageSize

        CREATE TABLE #PageIndex
        (
            [IndexID] INT IDENTITY (1, 1) NOT NULL,
            [ProductID] INT 
        )

        DECLARE @SQL AS NVARCHAR(4000)

        SET @SQL = 'INSERT INTO #PageIndex ([ProductID])'
        SET @SQL = @SQL + ' SELECT'
        IF @PageSize > 0
            SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)
        SET @SQL = @SQL + ' [ProductID]'
        SET @SQL = @SQL + ' FROM [dbo].[Product]'
        IF LEN(@WhereClause) > 0
            SET @SQL = @SQL + ' WHERE ' + @WhereClause
        IF LEN(@OrderBy) > 0
            SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

        EXEC (@SQL)

        SELECT
            TempTable.[ProductID], 
            TempTable.[ProductCode], 
            TempTable.[ProductName], 
            TempTable.[CategoryID], 
            TempTable.[UnitPrice]
        FROM
            [dbo].[Product] TempTable
        INNER JOIN
            #PageIndex PageIndex
        ON
            TempTable.[ProductID] = PageIndex.[ProductID]
        WHERE
            PageIndex.IndexID > @PageLowerBound
        AND
            PageIndex.IndexID <= @PageUpperBound
        ORDER BY
            PageIndex.IndexID

        SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
        SET @SQL = @SQL + ' FROM [dbo].[Product]'
        IF LEN(@WhereClause) > 0
            SET @SQL = @SQL + ' WHERE ' + @WhereClause

        EXEC (@SQL)
    END
GO

　　執行分頁儲存過程示例：

-- 不帶Where子句
EXECUTE [USP_Product_GetPaged] '', '[ProductID] ASC', 0, 10
EXECUTE [USP_Product_GetPaged] '', '[ProductID] ASC', 1, 10
-- Where子句
EXECUTE [USP_Product_GetPaged] '[UnitPrice] > 20', '[UnitPrice] DESC', 0, 10
EXECUTE [USP_Product_GetPaged] '[UnitPrice] > 20', '[UnitPrice] DESC', 1, 10

2 利用ROW_NUMBER()分頁

　　ROW_NUMBER()函式是SQL Server 2005新增的排名函式，可以使用ROW_NUMBER()進行分頁。

SELECT TOP (10) 
    [t].[ProductID] AS [ProductID], 
    [t].[ProductName] AS [ProductName]
FROM (
    SELECT
        [ProductID] AS [ProductID],
        [ProductName] AS [ProductName],
        ROW_NUMBER() OVER (ORDER BY [ProductID] ASC) AS [row_number]
    FROM [dbo].[Product]
    WHERE [UnitPrice] > 20
)  AS [t]
WHERE [t].[row_number] > 10
ORDER BY [t].[ProductID] ASC

3 利用OFFSET...FETCH分頁

　　OFFSET...FETCH是在SQL Server 2012中新增的實現分頁方式，OFFSET向SELECT查詢表明跳過多少行，FETCH表明從特定的位置開始檢索多少行。

　　示例：

SELECT [ProductID],[CategoryID],[ProductCode],[ProductName],[UnitPrice]
FROM [dbo].[Product]
ORDER BY [ProductID]
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

　　使用OFFSET...FETCH限制：

　　◊ 使用OFFSET...FETCH子句必須同時使用ORDER BY。

　　◊ 可以單獨使用OFFSET，但不能單獨使用FETCH。

　　◊ 不可以同時使用SELECT TOP 和 OFFSET...FETCH。

相關文章