SqlServer高效分页数据查询

 

参数说明：PageSize每页记录数 CurrentPage当前页数

降序效率最高分页查询
如果CurrentPage=1
SELECT TOP PageSize * FROM tb_user ORDER BY user_id DESC
否则
SELECT TOP PageSize *
FROM tb_user
WHERE (user_id <
          (SELECT MIN(user_id) AS MinID
         FROM (SELECT TOP PageSize*(CurrentPage-1) *
                 FROM tb_user
                 ORDER BY user_id DESC) temptable))
ORDER BY user_id DESC

降序次效率分页查询
SELECT *
FROM (SELECT TOP PageSize*currentPage *
        FROM tb_user
        ORDER BY user_id) temptable1
WHERE (user_id NOT IN
          (SELECT TOP PageSize*(CurrentPage-1) user_id
         FROM tb_user
         ORDER BY user_id))
ORDER BY user_id DESC

降序次效率分页查询（如果总记录数%每页记录数!=0,最后一页数据不正确）
SELECT TOP PageSize *
FROM (SELECT TOP PageSize*CurrentPage *
        FROM tb_user
        ORDER BY user_id) temptable
ORDER BY user_id DESC

 

以上代码经本人测试高效有效