首页 > 代码库 > 排序分页

排序分页

继续上一篇的行号分页,接下来讲讲排序分页。

数据库依旧是Northwind,表依旧是Orders,假设每页显示10条记录。

我们先查询第一页的记录。

SELECT TOP 10
        *
FROM    dbo.Orders
ORDER BY OrderID ASC

第二页的记录,先升序取出前20条记录,然后在这20条记录里倒序取出前10条记录,最后把这10条记录升序一下。

SELECT  *
FROM    ( SELECT TOP 10
                    *
          FROM      ( SELECT TOP 20
                                *
                      FROM      dbo.Orders
                      ORDER BY  OrderID ASC
                    ) T
          ORDER BY  T.OrderID DESC
        ) T2
ORDER BY T2.OrderID ASC

第三页的记录,先升序取出前30条记录,然后在这30条记录里倒序取出前10条记录,最后把这10条记录升序一下。

SELECT  *
FROM    ( SELECT TOP 10
                    *
          FROM      ( SELECT TOP 30
                                *
                      FROM      dbo.Orders
                      ORDER BY  OrderID ASC
                    ) T
          ORDER BY  T.OrderID DESC
        ) T2
ORDER BY T2.OrderID ASC

由此可以推断出,第N页记录为:

SELECT  *
FROM    ( SELECT TOP 10
                    *
          FROM      ( SELECT TOP N * 10
                                *
                      FROM      dbo.Orders
                      ORDER BY  OrderID ASC
                    ) T
          ORDER BY  T.OrderID DESC
        ) T2
ORDER BY T2.OrderID ASC

写成通用的SQL语句如下:

BEGIN
    DECLARE @PageSize INT     --每页显示条数
    DECLARE @PageIndex INT    --页码(从1开始)
    SET @PageSize = 10
    SET @PageIndex = 1
    SELECT  *
    FROM    ( SELECT TOP ( @PageSize )
                        *
              FROM      ( SELECT TOP ( @PageIndex * @PageSize )
                                    *
                          FROM      dbo.Orders
                          ORDER BY  OrderID ASC
                        ) T
              ORDER BY  T.OrderID DESC
            ) T2
    ORDER BY T2.OrderID ASC
END

 写成存储过程如下:

CREATE PROCEDURE P_GetPagedOrders3
    @PageSize INT ,              -- 每页显示条数
    @PageIndex INT ,             -- 页码(从1开始)
    @RecordCount INT OUTPUT ,    -- 数据总数
    @PageCount INT OUTPUT        -- 总页数
AS
    BEGIN
    -- 获取数据总数
        SELECT  @RecordCount = COUNT(1)
        FROM    dbo.Orders
        -- 计算总页数
        SET @PageCount = ( @RecordCount + @PageSize - 1 ) / @PageSize
        -- 获取当前页的数据
        IF @PageIndex < @PageCount -- 不是最后一页
            BEGIN
                SELECT  *
                FROM    ( SELECT TOP ( @PageSize )
                                    *
                          FROM      ( SELECT TOP ( @PageIndex * @PageSize )
                                                *
                                      FROM      dbo.Orders
                                      ORDER BY  OrderID ASC
                                    ) T
                          ORDER BY  T.OrderID DESC
                        ) T2
                ORDER BY T2.OrderID ASC
            END
        ELSE -- 最后一页
            BEGIN
                SELECT  *
                FROM    ( SELECT TOP ( @RecordCount % @PageSize )
                                    *
                          FROM      dbo.Orders
                          ORDER BY  OrderID ASC
                        ) T
                ORDER BY T.OrderID ASC
            END
    END

注意,最后一页需要特殊处理。

测试存储过程:

DECLARE    @RecordCount int,
        @PageCount int

EXEC    [dbo].[P_GetPagedOrders3]
        @PageSize = 10,
        @PageIndex = 2,
        @RecordCount = @RecordCount OUTPUT,
        @PageCount = @PageCount OUTPUT

SELECT    @RecordCount as N@RecordCount,
        @PageCount as N@PageCount

测试结果如下:

技术分享

 

排序分页