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。
适用:大部分分页查询需求。
相关文章
- 2023-01-03:超过5名学生的课。编写一个SQL查询来报告 至少有5个学生 的所有班级,返回结果不限顺序。请问sql语句如
- Gorm-原生 SQL 查询和执行(二)
- Oracle分页查询实战: 构建有效的SQL语句(oracle分页sql语句)
- 查询 MySQL查询:从SQL语句中构建你的查询(sql语句mysql)
- 排序按行排序查询SQL Server中的数据(sqlserver查询行)
- 语句Oracle精选经典SQL语句集锦(oracle经典sql)
- Sql Server 2005/2008 SqlCacheDependency查询通知的使用总结
- 如何使用MySQL查询并分析SQL计划(mysql查询sql计划)
- MySQL学习笔记:创建表的SQL语句(mysql创建表的sql)
- MySQL日志跟踪:管理SQL查询性能(mysql跟踪sql语句)
- 解读Oracle查询计划:优化SQL语句的必备技能(oracle解释计划)
- Sql Server 2005/2008 SqlCacheDependency查询通知的使用总结
- Oracle数据库:SQL文件的执行步骤详解(oracle如何执行sql文件)
- Oracle大法师:多条SQL串联,效率倍增!(oracle多条sql)
- 查询使用MSSQL对多表进行复杂SQL查询(sql mssql 多表)
- SQL Server中非空字段查询方法(mssql 非空 查询)
- MySQL实现SQL脚本查询与操作(mysql执行sql脚本)
- MSSQL查询SQL日志:深入了解服务器运行情况(mssql查询sql日志)
- 学习MySQL两表查询,掌握SQL连接操作(mysql两表查询的方法)
- 如何在MySQL中运行SQL查询(mysql上运行sql)
- Oracle SQL 三天转瞬即逝(oracle三天前sql)
- Oracle SQL认证获取高级计算能力的必要准备(oracle sql认证)
- Oracle SQL传参精准实现数据查询(oracle sql传参)
- SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
- oracle—SQL技巧之(一)连续记录查询sql案例测试
- Sql学习第三天——SQL关于CTE(公用表达式)的递归查询使用