效率比较高的分页存储过程

SQLServer适用

DECLARE @cid INT = 658842 ,
    @pageindex INT = 1 ,
    @pageCount INT = 10;

SELECT  *
FROM    ( SELECT TOP ( @pageCount * @pageindex )
                    ROW_NUMBER() OVER ( ORDER BY id DESC ) rownum ,
                    COUNT(1) OVER ( ) AS countnum ,
                    id AS newid
          FROM      tablename c WITH ( NOLOCK )
          WHERE     cid = @cid
        ) t1
        INNER JOIN tablename WITH ( NOLOCK ) ON id = t1.newid
WHERE   t1.rownum > ( @pageindex - 1 ) * @pageCount
ORDER BY t1.rownum;