一个实用的分页算法

最新推荐文章于 2024-09-14 22:25:23 发布

置顶 zfsCSD

最新推荐文章于 2024-09-14 22:25:23 发布

阅读量579

点赞数 1

文章标签：算法存储 null output

本文链接：https://blog.csdn.net/zfsCSD/article/details/5804458

版权

在用这个存储工程前需要update要查询的表名和每页的行数，当前查询的表是Person.Contact,每页行数设置为10

USE AdventureWorks
GO

CREATE PROCEDURE Page_Contact
@PageCommand nvarchar(10),

    --这两个参数需要特别说明一下：他们主要是用来让调用程序告诉存储过程，上次执行时返回页的第一条和最后一条记录，这样此次调用就可以方便的计算出要返回的行了，具体计算请看Next和Previous页的计算方法。所以程序调用次存储过程分页时，一定把返回行中的第一条和最后一条记录的ID赋给下一次调用。
    @FirstContactID int = null,
    @LastContactID int = null,

    @PageCount int output,
    @CurrentPage int output
AS
    SET NOCOUNT ON

    SELECT @PageCount = CEILING(COUNT(*)/10) FROM Person.Contact

    -- first page is requested OR previous page when the current
    -- page is already the first
    IF @PageCommand = 'FIRST' OR (@PageCommand = 'PREVIOUS' AND
        @CurrentPage <= 1)
    BEGIN
        SELECT TOP 10 *
        FROM Person.Contact
        ORDER BY ContactID

        SET @CurrentPage = 1

        RETURN 0
    END

    -- last page is requested or next page when the current
    -- page is already the last
    IF @PageCommand = 'LAST' OR (@PageCommand = 'NEXT' AND
        @CurrentPage >= @PageCount)
    BEGIN
        SELECT a.*
        FROM
            (SELECT TOP 10 *
            FROM Person.Contact
            ORDER BY ContactID DESC) a
        ORDER BY ContactID

        SET @CurrentPage = @PageCount

        RETURN 0
    END

    IF @PageCommand = 'NEXT'
    BEGIN
        SELECT TOP 10 *
        FROM Person.Contact
        WHERE ContactID > @LastContactID
        ORDER BY ContactID

        SET @CurrentPage = @CurrentPage+1

        RETURN 0
    END

    IF @PageCommand = 'PREVIOUS'
    BEGIN
        SELECT a.*
        FROM (
            SELECT TOP 10 *
            FROM Person.Contact
            WHERE ContactID < @FirstContactID
            ORDER BY ContactID DESC) a
        ORDER BY ContactID

        SET @CurrentPage = @CurrentPage-1

        RETURN 0
    END

    IF @PageCommand = 'GOTO'
    BEGIN
        IF @CurrentPage < 1
            SET @CurrentPage = 1
        ELSE IF @CurrentPage > @PageCount
            SET @CurrentPage = @PageCount

        DECLARE @RowCount int
        SET @RowCount = (@CurrentPage * 10)
--这句也要说明一下，之所以用EXEC来间接调用select语句是因为要把@RwoCount传进去，不这样，这个变量在select中无法识别
        EXEC ('SELECT * FROM
        (SELECT TOP 10 a.* FROM
        (SELECT TOP ' + @RowCount + ' * FROM Person.Contact ORDER BY ContactID) a
        ORDER BY ContactID DESC) b
        ORDER BY ContactID')

        RETURN 0
    END

    RETURN 1