首页 > 代码库 > 常用SQL Server分页方式
常用SQL Server分页方式
假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询每页30条,查询第1500页(即第45001-45030条数据),字段ID聚集索引,YEAR无索引,Sqlserver版本:2008R2
第一种方案、最简单、普通的方法:
SELECT TOP 30 * FROM [ARTICLE] WHERE ID NOT IN ( SELECT TOP 45000 ID FROM [ARTICLE] ORDER BY YEAR DESC ,ID DESC ) ORDER BY YEAR DESC ,ID DESC
第二种方案:
SELECT * FROM( SELECT TOP 30 * FROM ( SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC ,ID DESC ) f ORDER BY f.YEAR ASC ,f.ID DESC ) s ORDER BY s.YEAR DESC ,s.ID DESC
第三种方案:
SELECT * FROM ARTICLE w1 ,( SELECT TOP 30 ID FROM ( SELECT TOP 50030 ID ,YEAR FROM ARTICLE ORDER BY YEAR DESC ,ID DESC ) w ORDER BY w.YEAR ASC ,w.ID ASC ) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC ,w1.ID DESC
第四种方案:
SELECT * FROM ARTICLE w1 WHERE ID IN ( SELECT TOP 30 ID FROM ( SELECT TOP 45030 ID ,YEAR FROM ARTICLE ORDER BY YEAR DESC ,ID DESC ) w ORDER BY w.YEAR ASC ,w.ID ASC ) ORDER BY w1.YEAR DESC ,w1.ID DESC
第五种方案:
SELECT w2.n ,w1.* FROM ( SELECT TOP 50030 Row_number() OVER (ORDER BY YEAR DESC, ID DESC) n ,ID FROM ARTICLE ) w2 ,ARTICLE w1 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC
在查询页数靠前时,效率3>4>5>2>1,页码靠后时5>4>3>1>2,再根据用户习惯,一般用户的检索只看最前面几页,因此选择3 4 5方案均可,若综合考虑方案5是最好的选择
常用SQL Server分页方式
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。