SQL分页查询总结{转}
开发过程中经常遇到分页的需求,今天在此总结一下吧。
简单说来方法有两种,一种在源上控制,一种在端上控制。源上控制把分页逻辑放在SQL层;端上控制一次性获取所有数据,把分页逻辑放在UI上(如GridView)。显然,端上控制开发难度低,适于小规模数据,但数据量增大时性能和IO消耗无法接受;源上控制在性能和开发难度上较为平衡,适应大多数业务场景;除此之外,还可以根据客观情况(性能要求,源与端的资源占用等)在源和端之间加一层,应用特殊算法和技术进行处理。以下主要讨论源上,即SQL上的分页。
分页的问题其实就是在满足条件的一堆有序数据中截取当前所需要展示的那部分。实际上各种数据库都考虑到分页问题而内置了一些策略,比如MySql的LIMIT,Oracle的ROWNUM和ROW_NUMBER(),SqlServer的TOP和ROW_NUMBER(),基于此我们可以得到一系列分页的方法。
1、 基于MySql的LIMIT和Oracle的ROWNUM,可以直接限制返回区间(以MySql为例,注意使用Oracle的ROWNUM时要应用子查询):
方法一、直接限制返回区间
SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;
优点:写法简单。
缺点:当页码和页大小过大时,性能明显下降。
适用:数据量不大。
2、基于LIMIT(MySql)、ROWNUM(Oracle)和TOP(SqlServer),他们可以限制返回的行数,因此可以得到以下两套通用的方法(以SqlServer为例):
方法二、NOT IN
SELECT TOP 页大小 * FROM table WHERE 主键 NOT IN ( SELECT TOP (页码-1)*页大小 主键 FROM table WHERE 查询条件 ORDER BY 排序条件 ) ORDER BY 排序条件
优点:通用性强。
缺点:当数据量较大时向后翻页,NOT IN中的数据过大会影响性能。
适用:数据量不大。
方法三、MAX
SELECT TOP 页大小 * FROM table WHERE 查询条件 AND id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP ((页码-1)*页大小) id FROM table WHERE 查询条件 ORDER BY id ) AS tempTable ) ORDER BY id
优点:速度快,特别是当id为主键时。
缺点:适用面窄,要求排序条件单一且可比较。
适用:简单排序(特殊情况也可尝试转换成类似可比较值处理)。
3、基于SqlServer和Oracle的ROW_NUMBER(),可以得到返回数据的行号,基于此在限制返回区间得到如下方法(以SqlServer为例):
方法四、ROW_NUMBER()
SELECT TOP 页大小 * FROM ( SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件 ) AS tempTable WHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小 ORDER BY RowNum
优点:在数据量较大时相比NOT IN有优势。
缺点:小数据量时不如NOT IN。
适用:大部分分页查询需求。
相关文章
- 6. SQL 多表查询
- 【MySQL高级】优化SQL步骤
- SQL开发知识:Oracle递归查询start with connect by prior的用法解析
- SQL开发知识:关于表oracle的一些特殊查询sql语句
- Hibernate通过SQL查询常量时只返回第一个字符解决方法详解编程语言
- MySQL分页查询:实现快速简单的SQL语句(mysql分页查询sql语句)
- 日志探索Linux查看SQL数据库日志(linux查看sql)
- MySQL 跟踪SQL痕迹:如何调试查询问题(mysql跟踪sql)
- MySQL实现分页查询的SQL语句(mysql的分页sql)
- 玩转Redis:使用它替代SQL查询(redis实现sql查询)
- Sql Server 2005/2008 SqlCacheDependency查询通知的使用总结
- 深入浅出SQL Server简体中文版(sqlserver简体版)
- SQL Server内连接实现数据查询的简洁之道(sqlserver内连接)
- Oracle终结SQL:解救数据库之路(oracle终止sql)
- 脚本MSSQL自动生成SQL脚本:实现数据库快速保存(mssql生成sql)
- MySQL操作如何使用SQL窗口进行数据查询和修改(mysql 中sql窗口)
- 利用SQL查询Redis中的数据(用sql查询redis)
- MySQL中使用三个表进行联合查询的SQL语句(mysql三表联查sql)
- Oracle数据库加速发展把握SQL的优势(oracle使用sql吗)
- 查询快速精准查询Oracle SQL语句(oracle sql快捷)
- 查询Oracle SQL当天查询实战揭秘不一样的技术(oracle sql当天)
- 掌握Oracle SQL写法,激发你的SQL能力(oracle sql写法)
- Oracle SQL传参精准实现数据查询(oracle sql传参)
- SQL复合查询条件(AND,OR,NOT)对NULL值的处理方法
- sql查询本年、本月、本日记录的语句,附SQL日期函数
- SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
- SQL查询和删除重复字段数据的方法